比较来自世界各地的卖家的域名和 IT 服务价格

Sql 服务器参考请求 /group by 随着最大的存在。/

你好,

我有一个请求的巨大问题,这些是我在我的表中的数据:


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


所以我想这样做,这是为了获得最高的字符串 groupDepth, 指定时 entityId 和 groupId. 结果示例:


input: entityId = 294, groupId = 1090
entityId groupId groupDepth
-------------------- -------------------- -----------
294 1090 1


input: entityId = 113, groupId = 1090
entityId groupId groupDepth
-------------------- -------------------- -----------
NULL 1090 0


我想到了这样的事情:


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
/


任何帮助都会非常感激!
已邀请:

奔跑吧少年

赞同来自:

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


如果我正确理解你,应该工作

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

董宝中

赞同来自:

这个怎么样?


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

: 更新 SQL 订购 entityId 下降并采取第一个将为这些案例提供正确的答案。

要回复问题请先登录注册