SQL Server分页方法汇总
一直以来，SQL Server都是很多开发人员接触学习和使用最多的一个数据库，因为其简单而强大的管理工具，再加上不错的性能和超级高的使用场合。
记得刚毕业那两年时间里在一个小团队里写了很多Store Procedure和Report相关的SQL脚本，但一直对分页这个问题有一个清晰的理解，当然也可以根据Internet上的方法做出自己的分页代码，但总觉得应该好好研究一下。
一直以来，SQL Server都是很多开发人员接触学习和使用最多的一个数据库，因为其简单而强大的管理工具，再加上不错的性能和超级高的使用场合。
记得刚毕业那两年时间里在一个小团队里写了很多Store Procedure和Report相关的SQL脚本，但一直对分页这个问题有一个清晰的理解，当然也可以根据Internet上的方法做出自己的分页代码，但总觉得应该好好研究一下。
时光荏苒，如果离开那家小公司进入一个相对大一点儿软件公司也已三载有余，不过数据库方面的技能因为很少使用的原因，似乎已经退化到了学校的水平。有句老话说得好——出来混迟早要还，当现在想换工作的时候才发现三年来的失策。正所谓亡羊补牢，为时不晚，正好借此机会把之前落下的知识好好梳理一下，给自己一个再学习提高的机会。
首先，我们为什么要对数据进行分页处理？ （注：此部分内容来自邹建童鞋的《SQL Server 2000开发与管理》
- 减少服务器磁盘系统的读取压力（满足条件的数据很多，但一次只读取一部分）
- 减少网络流量，减轻网络的传输压力（仅在需要时传输部分数据）
- 减轻客户端显示处理的负担
- 提高处理效率
分页通常有两种方法：一是透过应用程序的组件进行分页（e.g. ADO/ADO.NET)；二便是在数据库中进行分页处理。本文的重点则是探讨在数据库中进行分布处理的方法。
因为在SQL Server 2005之前的版本里是没有记录号(i.e. ROW_NUMBER()函数)的，所以记录的定位需要依赖于数据自身，比如在表中定义主键或者唯一键。如果数据中没有可以进行记录定位的字段，则无法直接实现分布处理，这种情况可以使用SELECT INTO子句，配合标识列为记录增加一个唯一 键来解决。
把大量数据存入临时表中，是一个耗时的处理，建议为没有主键的表增加标识列做主键，如果不愿意使用IDENTITY列，而表中又没有主键，建议还是在应用程序中进行分页。
TOP N 分页
查询指定页的数据，作是查询出到该页为止的所有记录，再从结果中过滤掉该页以前的所有数据。
1. 对于只有一个主键的记录，如果列名为 pk_col
我们来一起看一个例子：
-- Create table only one primary key
CREATE TABLE persons
(
id int identity(1,1) NOT NULL,
username varchar(255) NOT NULL,
lastname varchar(255),
firstname varchar(255),
city varchar(255)
)
-- Prepare data
INSERT persons SELECT 'jack', 'Jack', 'Hu', 'Beijing'
UNION SELECT 'tony', 'Tony', 'Li', 'Nanjing'
UNION SELECT 'Jim', 'Jimmy', 'Yang', 'Shanghai'
UNION SELECT 'Ronnie', 'Ronnie', 'Gu', 'Shanghai'
UNION SELECT 'Eason', 'Eason', 'Chen', 'Hongkong'
UNION SELECT 'Paul', 'Paul', 'Zeng', 'FuZhou'
UNION SELECT 'Roy', 'Roy', 'Guo', 'Chengdu'
UNION SELECT 'Ryan', 'Ryan', 'Zheng', 'Zhengzhou'
UNION SELECT 'Brad', 'Brad', 'Kong', 'Nantong'
UNION SELECT 'Peter', 'Peter', 'Yang', 'Hangzhou'
UNION SELECT 'Raj', 'Raj', 'Ren', 'Changzhou'
UNION SELECT 'Harword', 'Harword', 'Wu', 'Wuxi'
UNION SELECT 'David', 'David', 'Li', 'Kaifeng'
UNION SELECT 'Scott', 'Scott', 'King', 'Shangqiu'
UNION SELECT 'Bill', 'Bill', 'Zheng', 'Xuzhou'
UNION SELECT 'Steve', 'Steve', 'Jobs', 'Benbu'
UNION SELECT 'Henry', 'Henry', 'Ye', 'Chouzhou'
UNION SELECT 'Jason', 'Jason', 'Xie', 'Zhenjiang'
UNION SELECT 'Simon', 'Simon', 'Xu', 'Yancheng'
UNION SELECT 'Leo', 'Leo', 'Zhou', 'Yanan'
UNION SELECT 'Jane', 'Jane', 'Li', 'Ankang'
UNION SELECT 'Terry', 'Terry', 'Shen', 'Baoji'
UNION SELECT 'Joe', 'Joe', 'Zheng', 'Guangzhou'
UNION SELECT 'Gary', 'Gary', 'Li', 'Foshan'
UNION SELECT 'Tim', 'Tim', 'Yi', 'Zhongshan'
UNION SELECT 'Tom', 'Tom', 'Ren', 'Nanchang'
UNION SELECT 'Emily', 'Emily', 'Wang', 'Wenzhou'
UNION SELECT 'Jacky', 'Jacky', 'Li', 'Ningbo'
UNION SELECT 'Helen', 'Helen', 'Zhang', 'Nantong'
a. 方法1： 此方法适用于SQL Server 2000/2005/2008
-- Pager the record
-- Note: N代表要求查询的第n页的记录，X表示每一页有x条记录。
select top X * from tb
where pk_col not in (select top (N-1)*X pk_col from tb)
-- Demo
select top 10 * from persons
where username not in(select top ((3-1)*10) username from persons)
b. 方法2： 此方法适用于SQL Server 2005/2008
-- Pager the record
-- NOTE: N代表要求查询的第N页的记录，X表示每一页有X条记录。
SELECT TOP X *
FROM (SELECT ROW_NUMBER() OVER (ORDER BY pk_col) AS RowNumber,*
FROM tb) A
WHERE RowNumber > X*(N-1)
-- DEMO
SELECT TOP 10 *
FROM (SELECT ROW_NUMBER() OVER (ORDER BY username) AS RowNumber,*
FROM persons) A
WHERE RowNumber > 10*(3-1)
其实对于SQL Server 2008，上面这种方法还可以有另一种写法：
-- Pager records
WITH orderednames AS
(
SELECT *, ROW_NUMBER() OVER (ORDER BY username) AS 'RowNumber'
FROM persons
)
SELECT *
FROM Orderednames
WHERE RowNumber BETWEEN 11 AND 20;
c. 方法3： 如果记录有自增列其实还可以用下面的方法来进行分页. 这种方法适用于SQL Server 2000/2005/2008
-- Pager the record
-- NOTE: N代表要求查询的第N页的记录，X表示每一页有X条记录。
select top X *
from tb
where id >
(
select isnull(max(id),0)
from (select top X*(N-1) id from tb order by id) a
)
order by id
2. 如果用于记录定位的列是多个列，即存在复合主键或者复合惟一键，此方法适用于SQL Server 2000/2005/2008
-- Pager the record
-- NOTE: N代表要求查询的第N页的记录，X表示每一页有X条记录。
select top X a.*
from tb a
left join (
select top ((N-1)*X)
pk_col1,
pk_col2,
...
pk_coln
from tb) b
on a.pk_col1 = b.pk_col1
and a.pk_col2 = b.pk_col2
...
and a.pk_coln = b.pk_coln
where b.pk_col1 is null
and b.pk_col1 is null
...
and b.pk_coln is null
-- DEMO
select top 10 a.*
from persons a
left join (
select top ((3-1)*10)
lastname,
firstname
from persons) b
on a.lastname = b.lastname
and a.firstname = b.firstname
where b.lastname is null
and b.firstname is null
基于上面的理论我们可以创建一个通用的分页处理存储过程，此存储过程参考邹建童鞋的SQL Server开发与管理。
-- =============================================
-- Author: 邹建
-- Description: 基于TOP N理论的通用分页存储过程
-- =============================================
Create PROCEDURE sp_PagerViewer
@tableName sysname, --要分布显示的表名
@fieldKey nvarchar(1000), --用于定位记录的主键或惟一键字段，可以是逗号分隔的多个字段
@pageCurrent int = 1, --要显示的页码
@pageSize int = 10, --每页的大小（记录数）
@fieldShow nvarchar(1000) = '', --以逗号分隔的要显示的字段列表，如果不指定，则显示所有字段
@fieldOrder nvarchar(1000) = '', --以逗号分隔的排序 ，可以指定在字段后面指定DESC/ASC用以指定排序顺序
@where nvarchar(1000) = '', --查询条件
@pageCount int output --总页数
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--检查对象是否有效
IF OBJECT_ID(@tableName) IS NULL
BEGIN
RAISERROR(N'对象"%s"不在在', 1, 16, @tableName)
RETURN
END
IF OBJECTPROPERTY(OBJECT_ID(@tableName), N'IsTable') = 0
AND OBJECTPROPERTY(OBJECT_ID(@tableName), N'IsView') = 0
AND OBJECTPROPERTY(OBJECT_ID(@tableName), N'IsTableFunction') = 0
BEGIN
RAISERROR(N'"%s"不是表、视图或者表值函数',1,16,@tableName)
RETURN
END
--分页字段检查
IF ISNULL(@fieldKey,N'') = ''
BEGIN
RAISERROR(N'分页处理需要主键或惟一键',1,16)
RETURN
END
--其他参数检查及规范
IF ISNULL(@pageCurrent, 0) < 1 SET @pageCurrent = 1
IF ISNULL(@pageSize, 0) < 1 SET @pageSize = 10
IF ISNULL(@fieldShow, N'') = N'' SET @fieldShow = N'*'
IF ISNULL(@fieldOrder, N'') = N''
SET @fieldOrder = N''
ELSE
SET @fieldOrder = N'ORDER BY ' + LTRIM(@fieldOrder)
IF ISNULL(@where, N'') = N''
SET @where = N''
ELSE
SET @where = N'WHERE (' + @where + N')'
--如果@pageCount为NULL值，则计算总页数(这样设计可以只在第一次计算总页数，以后调用时，把总页数传回给存储过程，避免再次计算总页数，对于不想计算总页数的处理而言，可以给@pageCount赋值)
IF @pageCount IS NULL
BEGIN
DECLARE @sql nvarchar(4000)
SET @sql = N'SELEcT @pageCount = COUNT(*)'
+ N' FROM ' + @tableName
+ N' ' + @where
EXEC sp_executesql @sql, N'@pageCount int OUTPUT', @pageCount OUTPUT
SET @pageCount = (@pageCount + @pageSize-1)/@pageSize
END
--查检当前参数是否超过了总页数，如果是将其设置为最后一页
IF @pageCurrent > @pageCount SET @pageCurrent = @pageCount
--计算分布显示的TOPN值
DECLARE @topN varchar(20), @topN1 varchar(20)
SELECT @topN = @pageSize, @topN1 = (@pageCurrent - 1) * @pageSize
--第一页直接显示
IF @pageCurrent = 1
EXEC(N'SELECT TOP ' + @topN
+ N' ' + @fieldShow
+ N' ' + @tableName
+ N' ' + @where
+ N' ' + @fieldOrder)
ELSE
BEGIN
--处理别名
IF @fieldShow = N'*'
SET @fieldShow = N'a.*'
--生成主键处理条件
DECLARE @where1 nvarchar(4000), @where2 nvarchar(4000), @s nvarchar(1000), @field sysname
SELECT @where1= N'', @where2= N'', @s = @fieldKey
WHILE CHARINDEX(N',', @s) > 0
SELECT @field = LEFT(@s, CHARINDEX(N',',@s) - 1),
@s = STUFF(@s, 1, CHARINDEX(N',', @s), N''),
@where1 = @where1 + N' AND a.' + @field + N'=b.' + @field,
@where2 = @where2 + N' AND b.' + @field + N' IS NULL',
@where = replace(@where, @field, N'a.' + @field),
@fieldOrder = REPLACE(@fieldOrder, @field, N'a.' + @field),
@fieldShow = REPLACE(@fieldShow, @field, N'a.' + @field)
SELECT @where = REPLAcE(@where, @s, N'a.' + @s),
@fieldOrder = REPLACE(@fieldOrder, @s, N'a.' + @s),
@fieldShow = REPLACE(@fieldShow, @s, N'a.' + @s),
@where1 = STUFF(@where1 + N' AND a.' + @s +N'=b.' + @s, 1, 5, N''),
@where2 = CASE
WHEN @where = '' THEN N'WHERE ('
ELSE @where + N' AND('
END+N'b.'+@s+N' IS NULL'+@Where2+N')'
--执行查询
EXEC(N'SELECT TOP '+@topN
+N' '+@fieldShow
+N' FROM '+@tableName
+N' a LEFT JOIN(SELECT TOP '+@topN1
+N' '+@fieldKey
+N' FROM '+@tableName
+N' a '+@where
+N' '+@fieldOrder
+N')b ON '+@where1
+N' '+@where2
+N' '+@fieldOrder)
END
END
GO
使用字符串分页
对于单主键(使用单个字段定位记录)的数据进行分页处理，可以使用字符串来解决TOPN方法中的问题。使用字符串实现分页处理的原理：
a. 在需要分页处理的数据中，查询到指定页截止的所有主键 (或者其他可以用于记录定位的字段)值。
b. 在查询过程中，使用一个字符串变量记录指定页的所有主键 (或者其他可以用于记录定位的字段)值列表。
c. 使用IN子句，配合记录指定页主键(或者其他可以用于记录定位的字段)值列表的字符串变量查询出最终结果。
下面是使用这种方法实现的通用分页存储过程：
-- =============================================
-- Author: 邹建
-- Description: 使用字符串的通用分布处理存储过程
-- =============================================
ALTER PROCEDURE sp_StringPagerViewer
@tableName sysname, --要分布显示的表名
@fieldKey nvarchar(1000), --用于定位记录的主键或惟一键字段，可以是逗号分隔的多个字段
@pageCurrent int = 1, --要显示的页码
@pageSize int = 10, --每页的大小（记录数）
@fieldShow nvarchar(1000) = '', --以逗号分隔的要显示的字段列表，如果不指定，则显示所有字段
@fieldOrder nvarchar(1000) = '', --以逗号分隔的排序 ，可以指定在字段后面指定DESC/ASC用以指定排序顺序
@where nvarchar(1000) = '', --查询条件
@pageCount int output --总页数
AS
BEGIN
DECLARE @sql nvarchar(4000)
SET NOCOUNT ON;
--查检对象是否有效
IF OBJECT_ID(@tableName) IS NULL
BEGIN
RAISERROR(N'对象"%s"不存在', 1, 16, @tableName)
RETURN
END
IF OBJECTPROPERTY(OBJECT_ID(@tableName), N'IsTable') = 0
AND OBJECTPROPERTY(OBJECT_ID(@tableName), N'IsView') = 0
AND OBJECTPROPERTY(OBJECT_ID(@tableName), N'IsTableFunction') = 0
BEGIN
RAISERROR(N'"%s"不是表、视图或者表值函数', 1, 16, @tableName)
RETURN
END
--分页字段检查
IF ISNULL(@fieldKey,N'') = ''
BEGIN
RAISERROR(N'分页处理需要主键或惟一键',1,16)
RETURN
END
--其他参数检查及规范
IF ISNULL(@pageCurrent, 0) < 1 SET @pageCurrent = 1
IF ISNULL(@pageSize, 0) < 1 SET @pageSize = 10
IF ISNULL(@fieldShow, N'') = N'' SET @fieldShow = N'*'
IF ISNULL(@fieldOrder, N'') = N''
SET @fieldOrder = N''
ELSE
SET @fieldOrder = N'ORDER BY ' + LTRIM(@fieldOrder)
IF ISNULL(@where, N'') = N''
SET @where = N''
ELSE
SET @where = N'WHERE (' + @where + N')'
--如果@pageCount为NULL值，则计算总页数(这样设计可以只在第一次计算总页数，以后调用时，把总页数传回给存储过程，避免再次计算总页数，对于不想计算总页数的处理而言，可以给@pageCount赋值)
IF @pageCount IS NULL
BEGIN
SET @sql = N'SELECT @pageCount = COUNT(*)'
+ N' FROM ' + @tableName
+ N' ' + @where
EXEC sp_executesql @sql, N'@pageCount int OUTPUT', @pageCount OUTPUT
SET @pageCount = (@pageCount + @pageSize-1)/@pageSize
END
--查检当前参数是否超过了总页数，如果是将其设置为最后一页
IF @pageCurrent > @pageCount SET @pageCurrent = @pageCount
--计算分页显示的TOPN值
DECLARE @topN varchar(20), @topN1 varchar(20)
SELECT @topN = @pageSize, @topN1 = @pageCurrent * @pageSize
--第一页直接显示
IF @pageCurrent = 1
EXEC(N' SELECT TOP ' + @topN
+ N' ' + @fieldShow
+ N' FROM ' + @tableName
+ N' ' + @where
+ N' ' + @fieldOrder)
ELSE
BEGIN
SELECT @pageCurrent = @topN1,
@sql = N'SELECT @n=@n-1, @s=CASE WHEN @n<' + @topN
+ N' THEN @s + N'','' + QUOTENAME(RTRIM(CAST(' + @fieldKey
+ N' as varchar(8000))), N'''''''') ELSE N'''' END FROM ' + @tableName
+ N' ' + @where
+ N' ' + @fieldOrder
SET ROWCOUNT @pageCurrent
EXEC sp_executesql @sql,
N'@n int, @s nvarchar(4000) OUTPUT',
@pageCurrent,
@sql OUTPUT
SET ROWCOUNT 0
IF @sql = N''
EXEC(N'SELECT TOP 0'
+ N' ' + @fieldShow
+ N' FROM ' + @tableName)
ELSE
BEGIN
SET @sql = STUFF(@sql, 1, 1, N'')
--执行查询
EXEC(N'SELECT TOP ' + @topN
+ N' ' + @fieldShow
+ N' FROM ' + @tableName
+ N' WHERE ' + @fieldKey
+ N' IN(' + @sql
+ N') ' + @fieldOrder)
END
END
END
GO
需要注意的是上面这个存储过程只适用于单主键(或使用单个字段定位记录)情形。
临时表分页
使用字符串处理分页，可以解决TOP N分页的效率问题，但只适用于单主键（使用单个字段定位记录）的数据分页处理，要处理多主键（需要使用多个字段才能定位记录）数据的分布可以考虑使用临时表缓存主键（或者其他可以用于记录定位的字段）值列表的处理方法，它的处理过程如下：
a. 查询分页处理数据中截止到指定页的所有主键（或者其他可以用于记录定位的字段）值，并且将查询结果保存到临时表中 。
b. 删除临时表中 到指定页的前一页的所有数据。
c. 使用EXISTS子句，联接临时表做条件查询，得到需要的记录（指定页的数据）。
下面是使用临时表实现分页处理的存储过程：
-- =============================================
-- Author: 邹建
-- Description: 使用临时表的通用分布处理存储过程
-- =============================================
CREATE PROCEDURE sp_TempDbPagerViewer
@tableName sysname, --要分布显示的表名
@fieldKey nvarchar(1000), --用于定位记录的主键或惟一键字段，可以是逗号分隔的多个字段
@pageCurrent int = 1, --要显示的页码
@pageSize int = 10, --每页的大小（记录数）
@fieldShow nvarchar(1000) = '', --以逗号分隔的要显示的字段列表，如果不指定，则显示所有字段
@fieldOrder nvarchar(1000) = '', --以逗号分隔的排序 ，可以指定在字段后面指定DESC/ASC用以指定排序顺序
@where nvarchar(1000) = '', --查询条件
@pageCount int output --总页数
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--查检对象是否有效
IF OBJECT_ID(@tableName) IS NULL
BEGIN
RAISERROR(N'对象"%s"不存在', 1, 16, @tableName)
RETURN
END
IF OBJECTPROPERTY(OBJECT_ID(@tableName), N'IsTable') = 0
AND OBJECTPROPERTY(OBJECT_ID(@tableName), N'IsView') = 0
AND OBJECTPROPERTY(OBJECT_ID(@tableName), N'IsTableFunction') = 0
BEGIN
RAISERROR(N'"%s"不是表、视图或者表值函数', 1, 16, @tableName)
RETURN
END
--分页字段检查
IF ISNULL(@fieldKey,N'') = ''
BEGIN
RAISERROR(N'分页处理需要主键或惟一键',1,16)
RETURN
END
--其他参数检查及规范
IF ISNULL(@pageCurrent, 0) < 1 SET @pageCurrent = 1
IF ISNULL(@pageSize, 0) < 1 SET @pageSize = 10
IF ISNULL(@fieldShow, N'') = N'' SET @fieldShow = N'*'
IF ISNULL(@fieldOrder, N'') = N''
SET @fieldOrder = N''
ELSE
SET @fieldOrder = N'ORDER BY ' + LTRIM(@fieldOrder)
IF ISNULL(@where, N'') = N''
SET @where = N''
ELSE
SET @where = N'WHERE (' + @where + N')'
--如果@pageCount为NULL值，则计算总页数(这样设计可以只在第一次计算总页数，以后调用时，把总页数传回给存储过程，避免再次计算总页数，对于不想计算总页数的处理而言，可以给@pageCount赋值)
IF @pageCount IS NULL
BEGIN
DECLARE @sql nvarchar(4000)
SET @sql = N'SELECT @pageCount = COUNT(*)'
+ N' FROM ' + @tableName
+ N' ' + @where
EXEC sp_executesql @sql, N'@pageCount int OUTPUT', @pageCount OUTPUT
SET @pageCount = (@pageCount + @pageSize-1)/@pageSize
END
--查检当前参数是否超过了总页数，如果是将其设置为最后一页
IF @pageCurrent > @pageCount SET @pageCurrent = @pageCount
--计算分页显示的TOPN值
DECLARE @topN varchar(20), @topN1 varchar(20)
SELECT @topN = @pageSize, @topN1 = @pageCurrent * @pageSize
--第一页直接显示
IF @pageCurrent = 1
EXEC(N' SELECT TOP ' + @topN
+ N' ' + @fieldShow
+ N' FROM ' + @tableName
+ N' ' + @where
+ N' ' + @fieldOrder)
ELSE
BEGIN
--生成主键（唯一键）处理条件
DECLARE @where1 nvarchar(4000), @s nvarchar(1000)
SELECT @where1=N'', @s=@fieldKey
WHILE CHARINDEX(N',', @s) > 0
SELECT @s=STUFF(@s,1,CHARINDEX(N',',@s),N''),
@where1 = @where1 + N' AND a.' + LEFT(@s,CHARINDEX(N',',@s)-1)
+ N'=' + LEFT(@s,CHARINDEX(N',',@s)-1)
SELECT @where1 = STUFF(@where1 + N' AND a.' + @s + N'=' + @s,1,5,N''),
@topN = @topN1 - @pageSize
--执行查询
EXEC(N'SET ROWCOUNT ' + @topN1
+ N' SELECT ' + @fieldKey
+ N' INTO # FROM ' + @tableName
+ N' ' + @where
+ N' ' + @fieldOrder
+ N' SET ROWCOUNT ' + @topN
+ N' DELETE FROM #'
+ N' SELECT ' + @fieldShow
+ N' FROM ' + @tableName
+ N' a WHERE EXISTS(SELECT * FROM # WHERE ' + @where1
+ N') ' + @fieldOrder)
END
END
GO
TODO: 这个存储过程还有点儿小问题,在处理多个主键的时候会报错
利用系统存储过程分页
之前的几种分页方法都是针对数据库中的具体对象(表,视图,表量函数)而言,还不能实现对任意TSQL语句的查询结果进行分页。接下来的分页存储过程可以对查询结果进行分页，主要使用游标来处理分页过程。
-- ===================================================
-- Author: 邹建
-- Description: 使用系统存储过程的通用分布处理存储过程
-- ===================================================
CREATE PROC sp_CursorPageerViewer
@sql ntext, --要执行的sql语句
@pageCurrent int=1, --要显示的页码
@pageSize int=10, --每页的大小
@pageCount int OUTPUT --总页数
AS
SET NOCOUNT ON
DECLARE @p1 int
--初始化分页游标
EXEC sp_cursoropen
@cursor=@p1 OUTPUT,
@stmt=@sql,
@scrollopt=1,
@ccopt=1,
@rowcount=@pageCount OUTPUT
--计算总页数
IF ISNULL(@pageSize,0)<1
SET @pageSize=10
SET @pageCount=(@pageCount+@pageSize-1)/@pageSize
IF ISNULL(@pageCurrent,0)<1 OR ISNULL(@pageCurrent,0)>@pageCount
SET @pageCurrent=1
ELSE
SET @pageCurrent=(@pageCurrent-1)*@pageSize+1
--显示指定页的数据
EXEC sp_cursorfetch @p1,16,@pageCurrent,@pageSize
--关闭分页游标
EXEC sp_cursorclose @p1
ROW_NUMBER()分页
ROW_NUMBER()函数是SQL Server 2005引入的一个函数，用来获取表或查询结果的rowid, 类似于Oracle中的RowID。 所以此分页代码只适用于SQL Server 2005以后的版本。
-- ===================================================
-- Description: 使用ROW_NUMBER()函数来给数据分页
-- ===================================================
DECLARE @pageCurrent AS INT, @pageSize AS INT
SET @pageCurrent = 3
SET @pageSize = 10
SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY id DESC) AS rownum,
username,lastname,firstname,city
FROM persons) AS a
WHERE rownum BETWEEN (@pageCurrent-1)*@pageSize+1
AND @pageCurrent * @pageSize
ORDER BY username DESC
下面是一个利用RowNumber函数来进行分页的通用存储过程：
-- ==============================================
-- Author: 黄剑平
-- Description: 利用RowNumber函数通用分页存储过程
-- ==============================================
CREATE PROCEDURE sp_RowNumbePagerViewer
@pageCurrent int = 1, --当前页码
@pageSize int = 10, --每页记录条数(页面大小)
@tableName nvarchar(500), --表名或视图名，甚至可以是嵌套SQL：(Select * From Tab Where ID>1000) Tab
@fieldShow nvarchar(200) = '*', --返回记录集字段名，","隔开，默认是"*"
@fieldOrder nvarchar(100) = 'ID ASC', --排序规则
@filter nvarchar(500), --过滤条件
@maxPage smallint output, --执行结果 -1 error, 0 false, maxpage true
@totalRow int output, --记录总数 /* 2007-07-12 22:11:00 update */
@description varchar(100) output --结果描述
AS
BEGIN
SET ROWCOUNT @pageSize;
SET @description = 'successful';
-------------------参数检测----------------
IF LEN(RTRIM(LTRIM(@tableName))) !> 0
BEGIN
SET @maxPage = 0;
SET @description = 'table name is empty';
RETURN;
END
IF LEN(RTRIM(LTRIM(@fieldOrder))) !> 0
BEGIN
SET @maxPage = 0;
SET @description = 'order is empty';
RETURN;
END
IF ISNULL(@pageSize,0) <= 0
BEGIN
SET @maxPage = 0;
SET @description = 'page size error';
RETURN;
END
IF ISNULL(@pageCurrent,0) <= 0
BEGIN
SET @maxPage = 0;
SET @description = 'page error';
RETURN;
END
-------------------检测结束----------------
BEGIN TRY
-- 整合SQL
DECLARE @SQL nvarchar(4000), @Portion nvarchar(4000);
SET @Portion = ' ROW_NUMBER() OVER (ORDER BY ' + @fieldOrder + ') AS ROWNUM FROM ' + @tableName;
SET @Portion = @Portion + (CASE WHEN LEN(@filter) >= 1 THEN (' Where ' + @filter + ') AS tab') ELSE (') AS tab') END);
SET @SQL = 'Select TOP(' + CAST(@pageSize AS nvarchar(8)) + ') ' + @fieldShow + ' FROM (Select ' + @fieldShow + ',' + @Portion;
SET @SQL = @SQL + ' Where tab.ROWNUM > ' + CAST((@pageCurrent-1) * @pageSize AS nvarchar(8));
-- 执行SQL, 取当前页记录集
Execute(@SQL);
--------------------------------------------------------------------
-- 整合SQL
SET @SQL = 'SET @Rows = (Select MAX(ROWNUM) FROM (Select' + @Portion + ')';
-- 执行SQL, 取最大页码
EXECUTE sp_executesql @SQL, N'@Rows int output', @totalRow output;
SET @maxPage = (CASE WHEN (@totalRow % @pageSize)<>0 THEN (@totalRow / @pageSize + 1)
ELSE (@totalRow / @pageSize) END);
END Try
BEGIN Catch
-- 捕捉错误
SET @maxPage = -1;
SET @description = 'error line: '
+ CAST(ERROR_LINE() AS varchar(8))
+ ', error number: '
+ CAST(ERROR_NUMBER() AS varchar(8))
+ ', error message: ' + ERROR_MESSAGE();
RETURN;
END Catch;
-- 执行成功
RETURN;
END
参考资料：
- 《SQL Server 2000开发与管理应用实例》 邹建
- http://www.cnblogs.com/psforever/archive/2011/08/16/2141665.html
