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

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

-- 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

 

推荐资料: