SQL Server ROW_NUMBER()在哪些情况下不从1开始？
我们在查询中使用 ROW_NUMBER()。它在几乎所有情况下都会返回正确的结果。但对于 1 个用户来说，它的行为非常不同。
With #TEST as (
select top 50
ROW_NUMBER() over (order by a.ID) as RN,
a.ID ID, a.Name Name
FROM a
where a.name like '%test%')
select *
from #TEST
where RN BETWEEN 1 AND 50
order by RN
当页面大小设置为 50 时，该查询对于该用户工作正常。但是当页面大小设置为 100 时，我们观察到它没有返回所有行。它只返回 10 行。即使有超过 100 个满足条件的结果。请查找以下无法正常工作的查询。
With #TEST as (
select top 100
ROW_NUMBER() over (order by a.ID) as RN,
a.ID ID, a.Name Name
FROM a
where a.name like '%test%')
select *
from #TEST
where RN BETWEEN 1 AND 100
order by RN
当尝试验证原因时，我们观察到第二个查询返回大于 100 的 RN 值。它不是从 1 开始。
有人可以解释这种行为的可能原因吗？语法中是否需要修改任何内容，或者 SQL Server 中是否需要更改任何设置以使 row_number() 函数值从 1 开始？
We are using ROW_NUMBER() in our query. It is returning correct results in almost all scenarios. But for 1 user, it is behaving very differently.
With #TEST as (
select top 50
ROW_NUMBER() over (order by a.ID) as RN,
a.ID ID, a.Name Name
FROM a
where a.name like '%test%')
select *
from #TEST
where RN BETWEEN 1 AND 50
order by RN
This query is working fine for that user when the page size is set as 50. But when the page size is set to 100, we observe that it is not returning all rows. It is just returning only 10 rows. Even though there are more than 100 results satisfying the condition. Please find the below query that is not working correctly.
With #TEST as (
select top 100
ROW_NUMBER() over (order by a.ID) as RN,
a.ID ID, a.Name Name
FROM a
where a.name like '%test%')
select *
from #TEST
where RN BETWEEN 1 AND 100
order by RN
When tried to verify for the reason, we observe that the 2nd query returns RN values greater than 100. It does not start from 1.
Can some one explain the probable reason for this behavior. Is there anything to be modified in the syntax or is there any setting to be changed in SQL Server for the row_number() function values to start from 1?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
row_number 始终以 1 开头。
您正在执行一个没有 order by 子句的
select top操作。这意味着您无法控制返回哪些行。您可能会得到不同的执行计划，它们使用不同的索引来获取行。一个计划中的前 100 行与另一计划中的前 100 行不同。将相关的 order by 添加到 CTE 中的查询中，或者您可以删除顶部子句，因为您无论如何都会过滤主查询中的行。row_number always starts with one.
You are doing a
select topwithout an order by clause. That means that you have no control over what rows are returned. You are probably getting a different execution plans that use different indexes to get the rows. The top 100 rows in one plan is not the same top 100 in another plan. Add a relevant order by to the query in the CTE or you can remove the top clause since you are filtering rows in the main query anyway.