SQLSERVER 中的几种分页方法
SQLSERVER中的几种分页方法
这些分页方法虽然不是最优的方案，但你可以当做sql语句练习，熟练掌握sql语句和分页原理，
当然，文章的最后，我也会给出一种最好的分页方法。
--测试数据库 CREATE TABLE STU ( ID INT NOT NULL PRIMARY KEY IDENTITY(1,1), NAME VARCHAR(40) ) --插入100一条数据 DECLARE @I INT SET @I=1 WHILE(@I<=100) BEGIN INSERT STU VALUES('JACK'+CAST( @I AS VARCHAR(3))) SET @I=@I+1; END
需求：(每页显示10条,取出第3页) PageSize=10 PageIndex=3
方法一：NOT IN 方式
SELECT TOP 10 * FROM STU WHERE ID NOT IN( SELECT TOP 20 ID FROM STU ORDER BY ID ASC ) ORDER BY ID --通用表达式 /* SELECT TOP PageSize * FROM TABLENAME WHERE ID NOT IN( SELECT TOP (PageIndex-1)*PageSize ID FROM STU ORDER BY ID ASC )ORDER BY ID ASC */
方法二：row_number 分页
SELECT * FROM ( SELECT ROW_NUMBER() OVER(ORDER BY ID) AS ROWID,* FROM STU ) AS T WHERE T.ROWID BETWEEN 21 AND 30 ORDER BY ID --通用表达式 /* SELECT * FROM ( SELECT ROW_NUMBER() OVER(ORDER BY ID) AS ROWID,* FROM TABLE_NAME )AS T WHERE T.ROWID BETWEEN (PageSize-1)*PageIndex+1 AND PageSize*PageIndex ORDER BY ID */
方法二的语句优化（加上了CTE）
WITH CET AS ( SELECT * ,ROW_NUMBER() OVER(ORDER BY ID) AS ROW_ID FROM STU ) SELECT ID,NAME FROM CET WHERE ROW_ID BETWEEN 10 AND 20
方法三:利用ID 大于多少
SELECT TOP 10 * FROM STU WHERE ID>( SELECT MAX(ID) FROM ( SELECT TOP 20 ID FROM STU ORDER BY ID ASC )AS T ) --通用表示式: /* SELECT TOP PageSize * FROM STU WHERE ID>( SELECT MAX(ID) FROM( SELECT TOP (PageIndex-1)*PageSize ORDER BY ID ASC ) AS T ) */
方法四：asc + desc + top 简称颠倒法把；
SELECT TOP 10 * FROM ( SELECT TOP 30 * FROM STU ORDER BY ID ASC )AS T ORDER BY T.ID DESC -- 通用表达式： /* SELECT TOP PageSize PageSize * FROM ( SELECT TOP PageSize*PageIndex FROM STU ORDER BY ID ASC ) AS T ORDER BY T.ID DESC */
方法五： IN （可以说值对方法四的一种改进）
SELECT TOP 10 * FROM STU WHERE ID IN( SELECT TOP 10 ID FROM ( SELECT TOP 30 ID FROM STU ORDER BY ID ASC ) AS T ORDER BY T.ID DESC ) /* 通用表达式： SELECT TOP PageSize * FROM TABLE WHERE ID IN( SELECT TOP PageSize ID FROM ( SELECT TOP PageSize*PageIndex FROM TABLE ORDER BY ID ASC )AS T ORDER BY T.ID DESC ) */
分页的方法还有很多，如使用游标，和变量表来分页的,
那些效率不是太好，这里就不总结了.
在实际的开发中，不仅仅是分页这个单一的需求，往往掺杂着根据相应的where条件分页
还有可排序的字段 返回记录总条数等等问题，
接下来，我们就做一个完整的存储过程，分页的存储过程，网上有很多的源码，使用的方法也有所不同，有说 row_number 效率高的，有说 top 方法高的，
不管哪种，还有的说，看情况，（具体点看这里：http://www.jb51.net/article/35213.htm）比如： 由此可见在查询页数靠前时，TOP 好一点，页码靠后时row_number好一点，应付百万级的数据都是没问题的，这里，总结一种经过测试的代码(会继续关注这个问题，有更好的代码，我会及时贴出.(总结)....)
(这里，两种方法我都写一下，你也试试吧~)
--第一种 row_number(注意SQL2000不支持row_number()函数,单我相信，应该没有人用sql2000 了 吧)
-- LIUQI 2015 9 28 --测试表 还是 stu CREATE PROC GETDATA( @recordTotal INT OUTPUT, --输出记录总数 @tableName VARCHAR(350), -- 表名 @fieldName VARCHAR(300)='*', --查询字段 @keyName VARCHAR(200)='ID', --索引字段 @pageSize INT =10, --每页记录调试 @pageIndex INT =1, --当前页 @orderString varchar(300)='ID', --排序条件 @whereString varchar(400)='1=1' --where条件 )AS BEGIN DECLARE @beginRow INT, @endRow INT, @tempLimit VARCHAR(200), @tempCount NVARCHAR(800), @SQL VARCHAR(800), @timeDiff DATETIME SET NOCOUNT ON SELECT @timeDiff=GETDATE(); SET @beginRow=(@pageIndex-1)*@pageSize +1 SET @endRow=@pageIndex*@pageSize; SET @tempLimit=' ROWS BETWEEN '+ CAST(@beginRow AS VARCHAR)+' AND '+CAST(@endRow AS VARCHAR); --输出总记录条数； --有中观点认为：当集合总数不会有变化时，没有必要每次都输出总记录条数 --第一次输出之后，应该把它保存起来，以备后是只用 SET @tempCount='SELECT @recordTotal=COUNT(*) FROM ( SELECT '+@keyName+' FROM '+@tableName+' WHERE '+@whereString +') AS MY_TEMP' EXEC SP_EXECUTESQL @tempCount,N'@recordTotal INT OUTPUT ',@recordTotal OUTPUT; --返回主要的结果结合； SET @SQL='SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY '+@orderString+') AS ROWS,'+@fieldName+' FROM '+@tableName+' WHERE '+@whereString+') AS MAIN_TEMP WHERE'+@tempLimit PRINT @SQL EXEC(@SQL) SELECT DATEDIFF(MS,@timeDiff,GETDATE()) AS MSTIME SET NOCOUNT OFF END GO --测试; DECLARE @TOTAL INT EXEC GETDATA @TOTAL OUTPUT,'STU',' ID NAME ',' ID ',10 ,3 SELECT @TOTAL --测试; DECLARE @TOTAL INT EXEC GETDATA @TOTAL OUTPUT,'STU',' ID NAME ',' ID ',10 ,3 SELECT @TOTAL /* 21 21 22 22 23 23 24 24 25 25 26 26 27 27 28 28 29 29 30 30 16 0 (MSTIME) 经过多次测试（本人的是普通pc机器），第一次都在10-16 左右，第二次查询同样的数据时，都是0 100 (TOTALCOUNT) */
第二种：TOP
ALTER PROC GETDATA2 ( @pageCount INT OUTPUT, --页数 @recordCount INT OUTPUT, --总数 @tableName VARCHAR(200)='STU',--表名 @KEY VARCHAR(100)='ID', --索引字段 @Fields VARCHAR(300)='*', --字段 @whereStr VARCHAR(400)='1=1', --where 条件 @orderStr VARCHAR(200)='ID', --排序字段 目前支持 单个字段 @Begin INT=0, --开始位置 @pageIndex INT =1, @pageSize INT =10 ) AS BEGIN SET NOCOUNT ON SET ANSI_WARNINGS ON IF (@pageSize<0 OR @pageIndex<0) BEGIN RETURN END DECLARE @NEW_WHERE1 VARCHAR(400), @NEW_ORDER1 VARCHAR(200), @NEW_ORDER2 VARCHAR(200), @SQL VARCHAR(800), @SQLCOUNT NVARCHAR(800), @TOP INT IF(@Begin<=0) SET @Begin=0 ELSE SET @Begin=@Begin-1; --这个部分 可以使用 WHERE 1=1 来进行替换 IF ISNULL(@whereStr,'')='' SET @NEW_WHERE1='' ELSE SET @NEW_WHERE1=' WHERE ' +@whereStr IF ISNULL(@orderStr,'')<>'' BEGIN SET @NEW_ORDER1='ORDER BY '+@orderStr +' ASC' SET @NEW_ORDER2=' ORDER BY T.'+@orderStr+' DESC ' END ELSE BEGIN SET @NEW_ORDER1=' ORDER BY ID ASC ' SET @NEW_ORDER2=' ORDER BY T.ID DESC ' END --接下来 获取总记录条数； SET @SQLCOUNT=N'SELECT @recordCount=COUNT(1),@pageCount=CEILING((COUNT(1)+0.0)/'+CAST(@pageSize AS VARCHAR )+') FROM '+@tableName+@NEW_WHERE1 EXEC SP_EXECUTESQL @SQLCOUNT,N'@recordCount INT OUTPUT,@pageCount INT OUTPUT',@recordCount OUTPUT,@pageCount OUTPUT --如果输入的当期页码大于实际总数； --则把实际总页数赋值给当前页数 IF @pageIndex>CEILING((@recordCount+0.0)/@pageSize) BEGIN SET @pageIndex=CEILING((@recordCount+0.0)/@pageSize) END --拼接sql语句； SET @SQL='SELECT '+@Fields+' FROM '+@tableName+' WHERE '+@KEY + ' IN (' +'SELECT TOP '+LTRIM(STR(@pageSize))+' '+@KEY +' FROM ' +'( ' +'SELECT TOP '+LTRIM(STR(@pageSize*@pageIndex+@Begin))+' '+@KEY+' FROM '+@tableName+@NEW_WHERE1+@NEW_ORDER1 +') AS T'+@NEW_ORDER2 +') ' PRINT @SQL SET NOCOUNT OFF END --测试: /* DECLARE @pageCount INT, @recordCount INT EXEC GETDATA2 @pageCount OUTPUT,@recordCount OUTPUT,'STU' go */
第二种，还有点地方，不太合理，需要整理下；.
