曾經用過的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