Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Группировка и суб запрос.  [new]
ErikI
Member

Откуда:
Сообщений: 162
Делю в запросе sub query. При попытке вывода получаю ошибку "2 Столбец "LOCATION.HID" недопустим в списке выбора, поскольку он не содержится ни в агрегатной функции, ни в предложении GROUP BY." Я же делаю sub query о чем мне говорит сервер?
                SELECT
                    ploc.id
                    ,(
                    		SELECT
                    		  STUFF(CAST((
                    		  	SELECT [text()] = '-' + [NAME]
                    		  	FROM LOCATION l
                    		  	WHERE cloc.hid.IsDescendantOf(l.hid) = 1
                    		FOR XML PATH(''), TYPE) AS VARCHAR(100)), 1, 1, '')
                      ) AS deliverToStr
                  FROM MATERIAL_TRANSFER mt
                    INNER JOIN MATERIAL_TRANSFER_CONTAINER mtc on mtc.MATERIAL_TRANSFER_ID = mt.ID
                    INNER JOIN MATERIAL_TRANSFER_SUBTASK mts on mtc.MATERIAL_TRANSFER_SUBTASK_ID = mts.ID
                    INNER JOIN MATERIAL_TRANSFER_STATUS mtst on mt.MATERIAL_TRANSFER_STATUS_ID = mtst.ID
                    INNER JOIN WR_SUBTASK subtask on subtask.id = mts.SUBTASK_ID
                    INNER JOIN WR_SUBTASK_TYPE subtaskType on subtaskType.id = subtask.subtask_type_id
                    INNER JOIN WR_TASK task on task.id = subtask.TASK_ID
                    INNER JOIN WR_TASK_TYPE taskType on taskType.id = task.task_type
                    INNER JOIN CONTAINER con on con.id = mtc.container_id
                    INNER JOIN LOCATION cloc ON cloc.ID = con.location_id
                    INNER JOIN LOCATION ploc ON ploc.ID = mt.DELIVER_TO_LOCATION_ID
                    INNER JOIN EAP_WORK_REQUEST wr on wr.id = con.work_request_id
                    LEFT JOIN EAP_USER us on us.id = mt.COMPLETED_BY_ID
                    LEFT JOIN EAP_CONTACTS contact on contact.id = us.contact_id
                    
                GROUP BY
                    ploc.id,
                    mt.id,
                    mt.barcode,
                    wr.barcode,
                    subtask.id,
                    subtask.task_id,
                    task_id,
                    taskType.name,
                    taskType.id,
                    subtaskType.name,
                    subtaskType.id,
                    mt.DELIVER_TO_LOCATION_ID,
                    mt.DELIVER_BY_DATE,
                    mt.ASSIGNED_TO_DEPARTMENT_ID,
                    mt.MATERIAL_TRANSFER_STATUS_ID,
                    mtst.name,
                    mt.COMPLETED_BY_ID,
                    contact.contact_name,
                    mt.COMPLETED_DATE
5 сен 16, 15:36    [19629247]     Ответить | Цитировать Сообщить модератору
 Re: Группировка и суб запрос.  [new]
ErikI
Member

Откуда:
Сообщений: 162
Запрос можно упростить. Суть в том, что ms sql проверяет наличие агрегирующих функций в sub query и если их нет отвергает запрос. Даже если он выдает 1 строку.
5 сен 16, 16:05    [19629441]     Ответить | Цитировать Сообщить модератору
 Re: Группировка и суб запрос.  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
В теории должно быть что-то похожее на это:
SELECT DISTINCT ploc.id
     , (
           SELECT STUFF(CAST((
                   SELECT [text()] = '-' + [Name]
                   FROM Location l
                   WHERE cloc.hid.IsDescendantOf(l.hid) = 1
                   FOR XML PATH (''), TYPE
               ) AS VARCHAR(100)), 1, 1, '')
       ) AS deliverToStr
FROM MATERIAL_TRANSFER mt
JOIN MATERIAL_TRANSFER_CONTAINER mtc ON mtc.MATERIAL_TRANSFER_ID = mt.id
JOIN CONTAINER con ON con.id = mtc.container_id
JOIN Location cloc ON cloc.id = con.location_id
5 сен 16, 16:22    [19629562]     Ответить | Цитировать Сообщить модератору
 Re: Группировка и суб запрос.  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8584
ErikI,

зачем Вам группировка, если нет в запросе ни одного агрегата. То, что написано, равносильно DISTINCT выражению.
5 сен 16, 18:44    [19630288]     Ответить | Цитировать Сообщить модератору
 Re: Группировка и суб запрос.  [new]
ErikI
Member

Откуда:
Сообщений: 162
Это упрощённый запрос. Единственный удобный вариант, это писать CLR Function. Всё остальное неудобно и не имеет части функционала.
6 сен 16, 08:57    [19631348]     Ответить | Цитировать Сообщить модератору
 Re: Группировка и суб запрос.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31868
ErikI
Это упрощённый запрос. Единственный удобный вариант, это писать CLR Function. Всё остальное неудобно и не имеет части функционала.
Так покажите неупрощённый :-)

В приведённом виде запрос абсурден по сути, да ещё и содержит синтаксические ошибки. А на указание этого вы пишите, что "он упрощённый, и нужно писать CLR Function" :-) Чтож, пишите CLR Function, если нужно.
6 сен 16, 09:13    [19631391]     Ответить | Цитировать Сообщить модератору
 Re: Группировка и суб запрос.  [new]
iljy
Member

Откуда:
Сообщений: 8711
ErikI
Это упрощённый запрос. Единственный удобный вариант, это писать CLR Function. Всё остальное неудобно и не имеет части функционала.



Так пишите, если это для вас единственно удобный. А ошибка у вас из-за использования cloc.hid в подзапросе.


AlanDenton, Владислав Колосов

DISTINCT - это прекрасно, вот только, в отличие от явной группировки, в этом случае под нее загонится и результат подзапроса, что не есть хорошо с точки зрения банальной производительности.
6 сен 16, 09:27    [19631427]     Ответить | Цитировать Сообщить модератору
 Re: Группировка и суб запрос.  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
iljy, не отрицаю что Вы правы. Но если брать во внимание первоначальный запрос, то DISTINCT самое меньшее зло.
6 сен 16, 09:31    [19631444]     Ответить | Цитировать Сообщить модератору
 Re: Группировка и суб запрос.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31868
AlanDenton
Но если брать во внимание первоначальный запрос, то DISTINCT самое меньшее зло.
Это не самое меньшее зло, а самый простой выход :-)
Чуть больше усилий (банальный подзапрос), и будет лучше читаться, будет производительнее, и без ошибок.
6 сен 16, 09:56    [19631555]     Ответить | Цитировать Сообщить модератору
 Re: Группировка и суб запрос.  [new]
ErikI
Member

Откуда:
Сообщений: 162
Переделал на CLR. Вот полный запрос.
                SELECT DISTINCT
                      SUM(IIF(cloc.HID.IsDescendantOf(ploc.HID) = 1, 1, 0)) AS containersTransferred
                    , SUM(IIF(cloc.HID.IsDescendantOf(ploc.HID) = 1, 0, 1)) AS containersRequested
                    , mt.id as transferId
                    , mt.barcode as barcode
                    , wr.barcode as wrBarcode
                    , subtask.id as subtaskId
                    , subtask.task_id as taskId
                    , taskType.name as task
                    , taskType.id as taskTypeId
                    , subtaskType.name as subtask
                    , subtaskType.id as subtaskTypeId
                    , mt.DELIVER_TO_LOCATION_ID as deliverTo
                    , (
                    		SELECT dbo.GROUP_CONCAT_D(l.name, '-')
                          FROM LOCATION l
                    		WHERE  cloc.hid.IsDescendantOf(l.hid) = 1
                      ) AS deliverToStr
                    , mt.DELIVER_BY_DATE as deliverBy
                    , mt.ASSIGNED_TO_DEPARTMENT_ID as assignedTo
                    , mt.MATERIAL_TRANSFER_STATUS_ID AS status
                    , mtst.name as statusName
                    , mt.COMPLETED_BY_ID as completedBy
                    , contact.contact_name as completedByStr

                    , mt.COMPLETED_DATE as completedDate
                  FROM MATERIAL_TRANSFER mt
                    INNER JOIN MATERIAL_TRANSFER_CONTAINER mtc on mtc.MATERIAL_TRANSFER_ID = mt.ID
                    INNER JOIN MATERIAL_TRANSFER_SUBTASK mts on mtc.MATERIAL_TRANSFER_SUBTASK_ID = mts.ID
                    INNER JOIN MATERIAL_TRANSFER_STATUS mtst on mt.MATERIAL_TRANSFER_STATUS_ID = mtst.ID
                    INNER JOIN WR_SUBTASK subtask on subtask.id = mts.SUBTASK_ID
                    INNER JOIN WR_SUBTASK_TYPE subtaskType on subtaskType.id = subtask.subtask_type_id
                    INNER JOIN WR_TASK task on task.id = subtask.TASK_ID
                    INNER JOIN WR_TASK_TYPE taskType on taskType.id = task.task_type
                    INNER JOIN CONTAINER con on con.id = mtc.container_id
                    INNER JOIN LOCATION cloc ON cloc.ID = con.location_id
                    INNER JOIN LOCATION ploc ON ploc.ID = mt.DELIVER_TO_LOCATION_ID
                    LEFT JOIN EAP_WORK_REQUEST wr on wr.id = con.work_request_id
                    LEFT JOIN EAP_USER us on us.id = mt.COMPLETED_BY_ID
                    LEFT JOIN EAP_CONTACTS contact on contact.id = us.contact_id
                    
                GROUP BY
                    mt.id
                    , mt.barcode
                    , wr.barcode
                    , subtask.id
                    , subtask.task_id
                    , task_id
                    , taskType.name
                    , taskType.id
                    , subtaskType.name
                    , subtaskType.id
                    , mt.DELIVER_TO_LOCATION_ID
                    , mt.DELIVER_BY_DATE
                    , mt.ASSIGNED_TO_DEPARTMENT_ID
                    , mt.MATERIAL_TRANSFER_STATUS_ID
                    , mtst.name
                    , mt.COMPLETED_BY_ID
                    , contact.contact_name
                    , mt.COMPLETED_DATE
6 сен 16, 18:20    [19634262]     Ответить | Цитировать Сообщить модератору
 Re: Группировка и суб запрос.  [new]
ErikI
Member

Откуда:
Сообщений: 162
Вообщем никак не удается выполнить запрос даже с DISTINCT!
6 сен 16, 18:36    [19634318]     Ответить | Цитировать Сообщить модератору
 Re: Группировка и суб запрос.  [new]
iap
Member

Откуда: Москва
Сообщений: 47085
ErikI
Вообщем никак не удается выполнить запрос даже с DISTINCT!
Вы придаёте какой-то волшебный смысл слову "DISTINCT"!
Зачем, например, DISTINCT и GROUP BY одновременно?
6 сен 16, 18:46    [19634353]     Ответить | Цитировать Сообщить модератору
 Re: Группировка и суб запрос.  [new]
iljy
Member

Откуда:
Сообщений: 8711
ErikI
Вообщем никак не удается выполнить запрос даже с DISTINCT!


Вы издеваетесь или у вас с чтением проблемы? Или просто методом тыка решили попробовать? Нахрена distinct вместе с группировкой? А проблема тут.
6 сен 16, 18:48    [19634360]     Ответить | Цитировать Сообщить модератору
 Re: Группировка и суб запрос.  [new]
ErikI
Member

Откуда:
Сообщений: 162
Проблем с чтением у меня нет. Если у вас проблемы, то можете просто не отвечать.
6 сен 16, 20:03    [19634520]     Ответить | Цитировать Сообщить модератору
 Re: Группировка и суб запрос.  [new]
ErikI
Member

Откуда:
Сообщений: 162
Кто знает как выполнять группировку с иерархическими запросами? Может обернуть в SQL sub запрос?
6 сен 16, 20:06    [19634524]     Ответить | Цитировать Сообщить модератору
 Re: Группировка и суб запрос.  [new]
ErikI
Member

Откуда:
Сообщений: 162
Нашел ответ поле cloc.hid тоже должно участвовать в группировеке.
7 сен 16, 09:42    [19635589]     Ответить | Цитировать Сообщить модератору
 Re: Группировка и суб запрос.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31868
ErikI
Нашел ответ поле cloc.hid тоже должно участвовать в группировеке.
Наконец то вы прочли сообщение сервера, и исправиили синтаксическую ошибку в запросе:
ErikI
При попытке вывода получаю ошибку "2 Столбец "LOCATION.HID" недопустим в списке выбора, поскольку он не содержится ни в агрегатной функции, ни в предложении GROUP BY."

А то какие то агрегатные функции, какие то дикие предположения о "Суть в том, что ms sql проверяет наличие агрегирующих функций в sub query и если их нет отвергает запрос", какие то CLR...
7 сен 16, 09:51    [19635640]     Ответить | Цитировать Сообщить модератору
 Re: Группировка и суб запрос.  [new]
o-o
Guest
серверное он читал, но только наполовину.
а вот ему в явном виде написали, и то не канает
iljy
ErikI
Это упрощённый запрос. Единственный удобный вариант, это писать CLR Function. Всё остальное неудобно и не имеет части функционала.



Так пишите, если это для вас единственно удобный. А ошибка у вас из-за использования cloc.hid в подзапросе.


AlanDenton, Владислав Колосов

DISTINCT - это прекрасно, вот только, в отличие от явной группировки, в этом случае под нее загонится и результат подзапроса, что не есть хорошо с точки зрения банальной производительности.
7 сен 16, 11:15    [19636201]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить