T-SQL SELECT DISTINCT & ROW_NUMBER() OVER 排序问题
我正在尝试使用 ROW_NUMBER() OVER 进行分页从视图中选择不同的行。当我将 ORDER BY 字段从 SMALLDATETIME 切换到 INT 时，我开始得到奇怪的结果：
SELECT RowId, Title, HitCount FROM
( SELECT DISTINCT Title, HitCount, ROW_NUMBER() OVER(ORDER BY HitCount DESC) AS RowId FROM ou_v_Articles T ) AS Temp
WHERE RowId BETWEEN 1 AND 5
此查询返回：
RowId | Title | HitCount
=======================
4 --- 9
1 --- 43
3 --- 11
2 --- 13
5 --- 0
结果显然顺序不正确。我不确定这里的问题是什么，但是当我删除 DISTINCT 时，它会正确排序它们。
谢谢。
I'm trying to select DISTINCT rows from a view using ROW_NUMBER() OVER for paging. When I switched the ORDER BY field from a SMALLDATETIME to INT I started getting weird results:
SELECT RowId, Title, HitCount FROM
( SELECT DISTINCT Title, HitCount, ROW_NUMBER() OVER(ORDER BY HitCount DESC) AS RowId FROM ou_v_Articles T ) AS Temp
WHERE RowId BETWEEN 1 AND 5
This query returns:
RowId | Title | HitCount
=======================
4 --- 9
1 --- 43
3 --- 11
2 --- 13
5 --- 0
The results are obviously not in the correct order. I'm not sure what the problem is here, but when I removed DISTINCT it orders them correctly.
Thanks.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
将 DISTINCT 应用于包含 ROW_NUMBER() 的列列表将始终导致每一行都是不同的，因为每行有一个 ROW_NUMBER。
Applying DISTINCT to a column list containing ROW_NUMBER() will always result in every row being distinct, as there is one ROW_NUMBER per row.
您获得的 RowId 值正确吗？也许您只需要外部查询上的 ORDER BY RowId 子句？
Is the RowId value you're getting correct? Perhaps you just need an ORDER BY RowId clause on the outer query?
您是否尝试过在外部选择上仅使用 order by 并删除 OVER 子句？
Have you tried just using an order by on the outer select and removing the OVER clause?