在进行一些查询的时候,如果返回的结果集很大,那么用户可能会希望对这些结果进行分页显示。也就是说,可以指定每一页显示多少条记录,以及要显示哪一页的记录。
以示例数据库AdventureWorks的Person.Contact表为例,现在希望显示该表中的人名以及其称呼。如果执行以下语句:
SELECT
Title, FirstName, Lastname
FROM Person.Contact
FROM Person.Contact
那么会一次过返回199720行记录,面对这么大的结果集真是头疼……还好我们可以把结果进行分页。
要实现分页需要用到嵌套子查询,该子查询就是上面的SELECT语句,不过我们还要为其加上一列,该列用数字为每一行顺序标识编号。这里要使用ROW_NUMBER()函数:
SELECT
ROW_NUMBER()
OVER
(
ORDER
BY
ContactID)
AS
RowNum, Title, FirstName, Lastname
FROM Person.Contact
FROM Person.Contact
有了顺序、唯一的编号,就可以在外部查询中进行分页,不过在这之前需要两个变量,表示每页显示多少行和页码。然后在在外部查询加一个WHERE子句和TOP子句。
完整的代码像这个样子:
DECLARE
@RowsPerPage
int
,
@PageIndex
int
SET @RowsPerPage = 10
SET @PageIndex = 1
SELECT sub.Title, sub.FirstName, sub.LastName
FROM
( SELECT ROW_NUMBER() OVER ( ORDER BY ContactID) AS RowNum, Title, FirstName, Lastname
FROM Person.Contact) AS sub
WHERE sub.RowNum BETWEEN ( @RowsPerPage * ( @PageIndex - 1 ) + 1 ) AND ( @RowsPerPage * @PageIndex )
SET @RowsPerPage = 10
SET @PageIndex = 1
SELECT sub.Title, sub.FirstName, sub.LastName
FROM
( SELECT ROW_NUMBER() OVER ( ORDER BY ContactID) AS RowNum, Title, FirstName, Lastname
FROM Person.Contact) AS sub
WHERE sub.RowNum BETWEEN ( @RowsPerPage * ( @PageIndex - 1 ) + 1 ) AND ( @RowsPerPage * @PageIndex )
这样就实现了分页功能,改变@RowsPerPage和@PageIndex的值即可看到效果。