SQL Server:如何在多个选择中获取行号
SQL Server:如何在多个选择中获取行号
提问于 2018-05-07 20:18:03
使用“多次选择”时出现错误行号
第一个查询得到正确的行号。
SELECT num
FROM (SELECT ItemId, CommentId,ROW_NUMBER() OVER (ORDER BY CreatedDate desc) AS num
From ItemComments
where ItemId = 10
) AS numbered
WHERE numbered.CommentId =100Scond查询获得正确的行号。
SELECT num
FROM (SELECT ItemId, CommentId, ROW_NUMBER() OVER(ORDER BY CreatedDate desc) AS num
From ItemComments
where ItemId = 20
) AS numbered
WHERE numbered.CommentId =200当我试图在一个查询中使用上述两个值时，我得到了错误的行号。
获取错误行号的第三个查询
SELECT num
FROM (SELECT ItemId, CommentId, ROW_NUMBER() OVER(ORDER BY createddate desc) AS num
From ItemComments
where ItemId in(20,10)
) AS numbered
WHERE numbered.CommentId in (200, 100)请帮助我在多重选择中获得正确的行号
谢谢
回答 1
Stack Overflow用户
回答已采纳
发布于 2018-05-07 20:20:26
你只需要partition by
SELECT num
FROM (SELECT ItemId, CommentId,
ROW_NUMBER() OVER (PARTITION BY ItemId ORDER BY createddate desc) AS num
From ItemComments
where ItemId in (20, 10)
) numbered
WHERE numbered.CommentId in (200, 100)页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/50221821
复制相关文章
相似问题
腾讯云开发者
