MSSQL 2008 R2 选择特定范围内的行 - 分页 - 最好的方法是什么

发布于 2024-12-05 21:11:31 字数 218 浏览 0 评论 0原文

目前这个sql查询能够在我确定的行之间进行选择。但有没有更好的方法呢？

  select * from (select *, ROW_NUMBER() over (order by Id desc) as RowId 
                 from tblUsersMessages ) dt
  where RowId between 10 and 25

原文

Currently this sql query is able to select between the rows i have determined. But are there any better approach for this ?

  select * from (select *, ROW_NUMBER() over (order by Id desc) as RowId 
                 from tblUsersMessages ) dt
  where RowId between 10 and 25

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

最单纯的乌龟 2024-12-12 21:11:31

取决于你的索引。

这可能会更好

SELECT *
FROM   tblUsersMessages
WHERE  Id IN (SELECT Id
              FROM   (select Id,
                             ROW_NUMBER() over (order by Id desc) as RowId
                      from   tblUsersMessages) dt
              WHERE  RowId between 10 and 25)

有时，如果存在可用于快速查找范围内的 Id 值的较窄索引，。查看我的请在此处回答以获取演示可能出现的问题类型的示例。

您需要针对您的具体情况检查SET STATISTICS IO ON的执行计划和输出。

Depends on your indexes.

Sometimes this can be better

SELECT *
FROM   tblUsersMessages
WHERE  Id IN (SELECT Id
              FROM   (select Id,
                             ROW_NUMBER() over (order by Id desc) as RowId
                      from   tblUsersMessages) dt
              WHERE  RowId between 10 and 25)

If a narrower index exists that can be used to quickly find the Id values within the range. See my answer here for an example that demonstrates the type of issue that can arise.

You need to check the execution plans and output of SET STATISTICS IO ON for your specific case.

回复收藏 0 原文

~没有更多了~