Sql Server - 查询帮助（使用 MAX 分组）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5908281/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Sql Server - Query help (group by with having MAX)
提问by MonkeyCoder
HI,
你好，
I'm having a huge problem with one query, this is the data that I have in my table:
我在一个查询中遇到了一个大问题，这是我的表中的数据：
entityId groupId groupDepth
-------------------- -------------------- -----------
NULL 1090 0
56 1090 1
222 1090 1
226 1090 1
227 1090 1
228 1090 1
234 1090 1
248 1090 2
249 1090 2
250 1090 2
251 1090 2
252 1090 1
256 1090 1
261 1090 1
288 1090 1
294 1090 1
300 1090 1
4691 1090 1
4694 1090 1
4697 1090 1
So what I would like to do, is to obtain the row with the highest groupDepth when given the entityId and groupId. Example results:
所以我想做的是在给定 entityId 和 groupId 时获取具有最高 groupDepth 的行。结果示例：
input: entityId = 294, groupId = 1090
entityId groupId groupDepth
-------------------- -------------------- -----------
294 1090 1
input: entityId = 113, groupId = 1090
entityId groupId groupDepth
-------------------- -------------------- -----------
NULL 1090 0
I was thinking about something like this:
我在想这样的事情：
SELECT * FROM [dbo].[EntityGroup] a
WHERE EXISTS
(
SELECT groupId
FROM [dbo].[EntityGroup] b
WHERE
(b.entityId is null or b.entityId = 294) AND
b.groupId = a.groupId
GROUP BY b.groupId
HAVING a.groupDepth = max(b.groupDepth) and
a.entityId = b.entityId
)
Any help will be greatly appreciated!
任何帮助将不胜感激！
回答by TBohnen.jnr
SELECT entityID, groupId, groupDepth
FROM EntityGroup t
WHERE groupDepth = (SELECT MAX(groupDepth) FROM EntityGroup e WHERE COALESCE(e.entityID,-1) = COALESCE(t.entityId,-1) AND e.groupId = t.groupID)
GROUP BY entityID, groupId, groupDepth
Should work if I understood you correctly
如果我正确理解你应该工作
EDIT
编辑
SELECT entityID, groupId, groupDepth
FROM EntityGroup t
WHERE groupDepth = (SELECT MAX(groupDepth) FROM @Temp e WHERE e.entityID = t.entityId AND e.groupId = t.groupID)
GROUP BY entityID, groupId, groupDepth
UNION
SELECT entityID, groupId, groupDepth
FROM EntityGroup t
WHERE groupDepth = (SELECT MAX(groupDepth) FROM @Temp e WHERE e.groupId = t.groupID AND e.entityId IS NULL) AND t.entityId IS NULL
GROUP BY entityID,groupId, groupDepth
回答by Catch22
How about this?
这个怎么样？
DECLARE @entityId INT = 294
DECLARE @groupId INT = 1090
SELECT TOP 1 entityId, groupid, Max(groupDepth) AS groupDepth
FROM EntityGroup
WHERE (entityId is null OR entityId = @entityId)
AND groupId = @groupId
GROUP BY entityId, groupId
order by entityId desc
EDIT: Updated SQL to order by entityId descending and take the first one will give the correct answer for the specified cases
编辑：更新 SQL 以按 entityId 降序排序并取第一个将为指定情况提供正确答案
