曾经用过的Sql Server分页方法小结

工作这些年, 数据库分页也用过几种方案, 总结如下:

1.用row_number函数及临时表分页, 适用sql 2005/2008.

大致思路如下:

SELECT *, ROW_NUMBER() OVER (ORDER BY  OrderID DESC  ) AS rownumber
into #SO
FROM tbl_SalesOrder
Where condition...

SELECT *
FROM #SO
Where (#SO.rownumber between 1 AND 15
order by #SO.RowNumber
 

2.用row_number函数及表表达式分页, 适用sql 2005/2008, 这个就不用临时表了.

要拼sql, 大致思路如下:

declare @SQLString varchar(2000)

set @SQLString='SELECT *, ROW_NUMBER() OVER (ORDER BY  OrderID DESC  ) AS rownumber
                          into #SO
                          FROM tbl_SalesOrder
                          Where condition...'

 set @SQLString = 'with tempTable as ('+ @SQLString +')'

 set @SQLString = @SQLString + ' Select * from tempTable where (rownumber between @FirstRec1 and @LastRec1) order by rownumber; '
 EXECUTE sp_executesql @SQLString

3.在没有row_number的早期版本时, 用临时表存储符合条件的全部记录并设置自增字段, 然后按照传入的页码返回相应的数据, 适用Sql 2000/2005/2008.

缺点是效率不高, 但很通用.

这倒是有个完整版:

ALTER PROCEDURE [dbo].[lzd_sp_getDocumentListBySQL]
@iPage int,
@iPageSize int,
@searchstring nvarchar(4000),
@orderstring nvarchar(4000),
@PageCount int output,
@RecordCount int output
AS
BEGIN
-- declare variables
DECLARE @iPageCount int -- total number of pages
DECLARE @iStart numeric -- start record
DECLARE @iEnd numeric -- end record

-- disable row counts
SET NOCOUNT ON

--建立临时表。
CREATE TABLE #Document       (
--这个自增字段十分关键，就是靠他来完成分页标示。
ID numeric(18, 0) IDENTITY,    
Num_InfoID numeric(18, 0) NOT NULL ,
Num_AdminID numeric(18, 0) NULL ,
VC_TITLE varchar (100)  NULL ,
Num_Type numeric(18,0) null,
Dt_Pub datetime NULL,
Vc_File varchar(100) null,
Num_DeptID numeric(18,0) null,
Vc_Content text null
)

--先转存到下面的这个纪录集。
exec(
'INSERT INTO #Document(Num_InfoID,Num_AdminID,VC_TITLE,Num_Type,Dt_Pub,Vc_File,Num_DeptID,Vc_Content)
SELECT Num_InfoID,Num_AdminID,VC_TITLE,Num_Type,Dt_Pub,Vc_File,Num_DeptID,Vc_Content
FROM dbo.Tbl_Document ' + @searchstring + ' order by ' + @orderstring
)     
--计算记录总数
SELECT @iPageCount = COUNT(*)
FROM  #Document

SELECT @RecordCount = @iPageCount

SELECT @iPageCount = CEILING(@iPageCount / @iPageSize) + 1

-- 检查页号是否合法
IF @iPage < 1
SELECT @iPage = 1

IF @iPage > @iPageCount
SELECT @iPage = @iPageCount

-- 计算开始和结束记录位置
SELECT @iStart = (@iPage - 1) * @iPageSize
SELECT @iEnd = @iStart + @iPageSize + 1

--这条sql语句就是选取固定的纪录集。
SELECT Num_InfoID,Num_AdminID,VC_TITLE,Num_Type,Dt_Pub,Vc_File,Num_DeptID,Vc_Content
FROM #Document
WHERE ID > @iStart
AND ID < @iEnd

DROP TABLE #Document

SELECT @PageCount =@iPageCount
-- turn back on record counts
SET NOCOUNT OFF
-- Return the number of records left
RETURN @PageCount
END

4.使用Top分页

这个思路网上看来的, 没实际用过, 也是一种思路, 看起来也是要拼sql的.

大致思路如下: 将符合条件的数据的前几页数据id提取出来, 然后top PageRowCount 并且not in这些id.

SELECT TOP @PageRowCount *
FROM tbl_user
WHERE
(
Num_LoginID NOT IN (SELECT TOP (@PageRowCount*(@CurrentPage-1)) Num_LoginID FROM Tbl_User ORDER BY Num_LoginID DESC)
)
ORDER BY Num_LoginID DESC

推荐资料:

http://tech.it168.com/msoft/2008-02-18/200802181013281_1.shtml
http://blog.csdn.net/lihonggen0/article/details/103511