Paging Query Using RowNumber Function

SQL Server 分页查询技巧

最新推荐文章于 2025-05-19 22:28:31 发布

原创最新推荐文章于 2025-05-19 22:28:31 发布 · 174 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#SQL Server #SQL

SqlServer 专栏收录该内容

1 篇文章

订阅专栏

本文介绍了一种在 SQL Server 中进行高效分页查询的方法，使用 ROW_NUMBER() 函数配合 ORDER BY 子句来实现对记录按开始时间和创建时间排序后的分页查询。这种方法自 SQL Server 2005 版本起可用。

Although we have many ways to do paging query in SQL Server (with 'not exist', 'not in'), SQL Server provide an inner function to help us to do paging query after SQL Server 2005. Below is a very simple example.

Conditions
1.We have a Table1,
2.We want to do paging query on records order by start time then by create time.
3.We'd like to query records between startIndex and endIndex.



SELECT * FROM ( 
			SELECT ROW_NUMBER() OVER (ORDER BY [t0].StartTime desc, [t0].CreateTime desc )
                              AS [ROW_NUMBER], [t0].*                       
                              FROM table1 AS [t0]) 
                          AS [t1]                       
                          WHERE [t1].[ROW_NUMBER] BETWEEN {startIndex} AND {endIndex}
                          ORDER BY [t1].[ROW_NUMBER];