#sql #sql-server #tsql
#sql #sql-сервер #tsql
Вопрос:
SELECT l.LocID, COUNT(ulr.UserID)
FROM Locations l
LEFT OUTER JOIN UserLocationRights ulr ON l.LocID = ulr.LocID
LEFT OUTER JOIN Devices d ON l.LocID = d.LocID
LEFT OUTER JOIN UserModelRights umr ON d.ModelName = umr.ModelName
AND ulr.UserID = umr.UserID
GROUP BY l.LocID, ulr.UserID, d.ModelName
ORDER BY l.LocID, ulr.UserID
Я хочу, чтобы результатами были LocID все записи в Locations , а во втором столбце было количество пользователей, имеющих права на это местоположение, которые также имеют права хотя бы на один из Devices in Location , который определяется записями в UserModelRights .
Я могу только понять, как получить то, что я хочу, вот так:
SELECT l.LocID, IsNull(UserHasModelRightInLoc.UserCount, 0) UserCount
FROM Locations l
LEFT OUTER JOIN (
SELECT ulr.LocID, COUNT(UserModelRightsPerLocation.UserID) UserCount
FROM UserLocRights ulr
INNER JOIN (
SELECT l.LocID, umr.UserID
FROM UserModelRights umr
INNER JOIN Devices d ON umr.ModelName = d.ModelName
INNER JOIN Locations l ON d.LocID = l.LocID
GROUP BY umr.ModelName, umr.UserID, l.LocID
) UserModelRightsPerLocation ON ulr.LocID = UserModelRightsPerLocation.LocID
AND ulr.UserID = UserModelRightsPerLocation.UserID
GROUP BY ulr.LocID
) UserHasModelRightInLoc ON l.LocID = UserHasModelRightInLoc.LocID
ORDER BY l.LocID
Я не знаю, возможно ли это, но я предполагаю, что может быть способ GROUP BY получить то, что я хочу, используя мой первый, гораздо меньший запрос. Проблема в том, что я думаю, что мне нужно выполнить несколько GROUP BY в определенном порядке, и я не знаю, возможно ли это или имеет смысл в SQL.
Есть ли способ получить желаемый результат без использования агрегатной функции? Если нет, может быть, сузить его до одного?
Комментарии:
1. Можете ли вы показать образцы данных и желаемые результаты? Просто перепроектировать ваш запрос, чтобы написать другую версию, перебросить его через стену и угадать, по-прежнему ли он дает правильные результаты, будет утомительно.
2. Нет, невозможно ПОДСЧИТАТЬ без использования агрегатной функции, и зачем вам это нужно?
3. Как ожидать агрегирования без агрегированной функции?
4.
"there's no way to COUNT without using an aggregate function": Это неверно @TabAlleman. Возможно, агрегат — лучшее решение, но вы можете РАССЧИТЫВАТЬ, используя множество других вариантов. Например, используя ROW_NUMBER() или функции ранжирования с TOP 1. Опять же, я не говорю, что это правильное решение, но только то, что ПОДСЧЕТ может быть выполнен без агрегата.5. @Eric, я прокомментировал не вопрос, а неверное предположение, что вы должны использовать агрегатную функцию для ПОДСЧЕТА. Как только OP предоставит DDL DML, я отвечу на этот вопрос… ПОДСЧЕТ без агрегата:
DECLARE @T TABLE (x int); INSERT @T(x) VALUES (2),(5),(7),(6); WITH MyCTE as (SELECT RN = ROW_NUMBER() OVER (ORDER BY x DESC) FROM @T) SELECT TOP 1 RN FROM MyCTE ORDER BY RN DESC
Ответ №1:
Я нахожу это немного сложным для понимания без выборки данных. Но, основываясь на вашем описании, это может сделать то, что вы хотите:
SELECT l.LocID, COUNT(DISTINCT ulr.UserID)
FROM Locations l LEFT JOIN
UserLocationRights ulr
ON l.LocID = ulr.LocID LEFT JOIN
Devices d
ON l.LocID = d.LocID LEFT JOIN
UserModelRights umr
ON d.ModelName = umr.ModelName AND
ulr.UserID = umr.UserID
GROUP BY l.LocID
ORDER BY l.LocID;
Комментарии:
1. Это звучит очень близко к тому, что я пытался сделать, если не совсем точно. Единственная проблема в том, что я получаю разные значения, чем мой запрос в моей операционной системе, когда возвращаются агрегатные функции, и я пока не уверен, почему…
2. Я получил результаты для сопоставления, изменив его на это:
SELECT l.LocID, COUNT(DISTINCT ulr.UserID) FROM LocStruct l LEFT JOIN Devices d ON l.LocID = d.LocID LEFT JOIN UserModelRights umr ON d.ModelName = umr.ModelName LEFT JOIN UserLocRights ulr ON l.LocID = ulr.LocID AND ulr.UserID = umr.UserID GROUP BY l.LocID ORDER BY l.LocID;