MSSQL 分頁處理
分頁的技巧有兩種，一種是直接透過 T-SQL，另一種是透過 Store Procedure假設 Northwind 有一個 Customer的 Table，你需要取回 41~50 筆的記錄，T-SQL語法該如何作呢？
[b]T-SQL：[/b][code]Select Top 10 CustomerID,CompanyName,ContactName,Country
from Customers
where CustomerID Not in (Select top 40 CustomerID from Customers order by Country,CustomerID)
Order by Country,CustomerID[/code][b]Store Procedure：[/b][code]CREATE PROCEDURE northwind_OrdersPaged
(
@PageIndex int,
@PageSize int
)
AS
BEGIN
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @RowsToReturn int
-- First set the rowcount
SET @RowsToReturn = @PageSize * (@PageIndex + 1)
SET ROWCOUNT @RowsToReturn
-- Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1
-- Create a temp table to store the select results
CREATE TABLE #PageIndex
(
IndexId int IDENTITY (1, 1) NOT NULL,
OrderID int
)
-- Insert into the temp table
INSERT INTO #PageIndex (OrderID)
SELECT OrderID
FROM Orders
ORDER BY OrderID DESC
-- Return total count
SELECT COUNT(OrderID) FROM Orders
-- Return paged results
SELECT O.*
FROM Orders O, #PageIndex PageIndex
WHERE O.OrderID = PageIndex.OrderID
AND PageIndex.IndexID > @PageLowerBound
AND PageIndex.IndexID < @PageUpperBound
ORDER BY PageIndex.IndexID
END
[/code]資料參考 [url]http://blog.sina.com.tw/4907/article.php?pbgid=4907&entryid=3921[/url] SQL Server 2000 Paging and Sorting Using ROWCOUNT and SQL_VARIANT
[url]http://www.codeproject.com/KB/database/SQLServer2KPagingSorting.aspx[/url][code]CREATE PROCEDURE dbo.up_GetSortedSalesOrdersByPageUsingRowset
@orderedOnStart datetime,
@orderedOnEnd datetime,
@pageNumber int,
@pageSize int,
@sortExpression varchar(100),
@sortOrder varchar(4),
@virtualCount int OUTPUT
AS
/*
Make sure that the page number is at least 1
*/
IF @pageNumber < 1
BEGIN
SET @pageNumber = 1
END
SELECT
@virtualCount = COUNT(*)
FROM
Sales.SalesOrderHeader Header
WHERE
Header.[OrderDate] >= @orderedOnStart
AND Header.[OrderDate] < @orderedOnEnd
DECLARE @lastKeyValue numeric(18,0)
DECLARE @lastAscendingSortValue SQL_Variant
DECLARE @lastDescendingSortValue SQL_Variant
DECLARE @numberToIgnore int
SET @numberToIgnore = (@pageNumber-1) * @pageSize
IF @numberToIgnore > 0
BEGIN
/*
Get the last available sort data and unique key
value from the last page.
*/
SET ROWCOUNT @numberToIgnore
SELECT
@lastKeyValue = [UniqueValue],
@lastAscendingSortValue = [AscendingSort],
@lastDescendingSortValue = [DescendingSort]
FROM
(
SELECT
Header.[SalesOrderID] AS [UniqueValue],
CASE
WHEN
UPPER(@sortOrder) = 'DESC'
AND UPPER(@sortExpression) = 'CUSTOMERID'
THEN
CONVERT(SQL_Variant, [CustomerID])
WHEN
UPPER(@sortOrder) = 'DESC'
AND UPPER(@sortExpression) = 'TOTALDUE'
THEN
CONVERT(SQL_Variant, [TotalDue])
WHEN
UPPER(@sortOrder) = 'DESC'
AND UPPER(@sortExpression) = 'ORDERDATE'
THEN
CONVERT(SQL_Variant, [OrderDate])
ELSE
NULL
END AS [DescendingSort],
CASE
WHEN
UPPER(@sortOrder) = 'ASC'
AND UPPER(@sortExpression) = 'CUSTOMERID'
THEN
CONVERT(SQL_Variant, [CustomerID])
WHEN
UPPER(@sortOrder) = 'ASC'
AND UPPER(@sortExpression) = 'TOTALDUE'
THEN
CONVERT(SQL_Variant, [TotalDue])
WHEN
UPPER(@sortOrder) = 'ASC'
AND UPPER(@sortExpression) = 'ORDERDATE'
THEN
CONVERT(SQL_Variant, [OrderDate])
ELSE
NULL
END AS [AscendingSort]
FROM
Sales.SalesOrderHeader Header
WHERE
Header.[OrderDate] >= @orderedOnStart
AND Header.[OrderDate] < @orderedOnEnd
) AS Derived
ORDER BY
[AscendingSort] ASC,
[DescendingSort] DESC,
[UniqueValue] ASC
END
/*
Select the first @pageSize records that come after the last sort
data/unique value from the last page. If this is the first page,
just get the first @pageSize records.
*/
SET ROWCOUNT @pageSize
SELECT
[SalesOrderID],
[OrderDate],
[TotalDue],
[CustomerID]
FROM
(
SELECT
[SalesOrderID],
[OrderDate],
[TotalDue],
[CustomerID],
[SalesOrderID] As [UniqueValue],
CASE
WHEN
UPPER(@sortOrder) = 'DESC'
AND UPPER(@sortExpression) = 'CUSTOMERID'
THEN
CONVERT(SQL_Variant, [CustomerID])
WHEN
UPPER(@sortOrder) = 'DESC'
AND UPPER(@sortExpression) = 'TOTALDUE'
THEN
CONVERT(SQL_Variant, [TotalDue])
WHEN
UPPER(@sortOrder) = 'DESC'
AND UPPER(@sortExpression) = 'ORDERDATE'
THEN
CONVERT(SQL_Variant, [OrderDate])
ELSE
NULL
END AS [DescendingSort],
CASE
WHEN
UPPER(@sortOrder) = 'ASC'
AND UPPER(@sortExpression) = 'CUSTOMERID'
THEN
CONVERT(SQL_Variant, [CustomerID])
WHEN
UPPER(@sortOrder) = 'ASC'
AND UPPER(@sortExpression) = 'TOTALDUE'
THEN
CONVERT(SQL_Variant, [TotalDue])
WHEN
UPPER(@sortOrder) = 'ASC'
AND UPPER(@sortExpression) = 'ORDERDATE'
THEN
CONVERT(SQL_Variant, [OrderDate])
ELSE
NULL
END AS [AscendingSort]
FROM
Sales.SalesOrderHeader Header
WHERE
Header.[OrderDate] >= @orderedOnStart
AND Header.[OrderDate] < @orderedOnEnd
) Derived
WHERE
(
@lastAscendingSortValue IS NULL
AND @lastDescendingSortValue IS NULL
AND @lastKeyValue IS NULL
)
OR
(
(@lastAscendingSortValue IS NOT NULL)
AND
(
([AscendingSort] > @lastAscendingSortValue)
OR
(
[AscendingSort] = @lastAscendingSortValue
AND [UniqueValue] > @lastKeyValue
)
)
)
OR
(
(@lastDescendingSortValue IS NOT NULL)
AND
(
([DescendingSort] < @lastDescendingSortValue)
OR
(
[DescendingSort] = @lastDescendingSortValue
AND [UniqueValue] > @lastKeyValue
)
)
)
ORDER BY
[AscendingSort] ASC,
[DescendingSort] DESC,
[SalesOrderID] ASC
SET ROWCOUNT 0
GO[/code] [code]--有排序，又稱【夾擠式分頁查詢】。MS-SQL 2000，常見的分頁用語法。
SELECT * FROM
(
SELECT TOP [每頁長度] * FROM
(
SELECT TOP [每頁長度*第幾頁] [欄位1, 欄位2, ...]
FROM [資料表]
WHERE [篩選條件]
ORDER BY [排序欄位] [DESC/ASC]
)
AS [資料表别名1]
ORDER BY [排序欄位] [ASC/DESC]
)
AS [資料表别名2]
ORDER BY [排序欄位] [DESC/ASC][/code][code]--MS-SQL 2005以後才有RANK()語法
SELECT * FROM
(
SELECT rank() OVER ( ORDER BY [排序欄位] [ASC/DESC] ) AS RankNumber, * FROM
(
SELECT [欄位1, 欄位2, ...]
FROM [資料表]
WHERE [篩選條件]
)
AS [資料表别名1]
) AS [資料表别名2]
WHERE RankNumber between [每頁長度*(第幾頁-1)] and [每頁長度*第幾頁][/code]資料參考 [url]http://www.player.idv.tw/prog/index.php?title=SQL#MS-SQL[/url] [code]ALTER PROCEDURE [dbo].[sp_page_test]
@rows_per_page int,
@current_page int
AS
DECLARE @start_row_num int
BEGIN
SET @start_row_num = (@current_page) * @rows_per_page --設定從第幾個record開始讀
WITH t AS
(
SELECT ROW_NUMBER() OVER(ORDER BY DOCUMENT_ID) AS row_number, DOCUMENT_ID, DOCUMENT_NAME
FROM DM_DOCUMENT
) --加入ROW_NUMBER()模擬成另一個table
SELECT * FROM t
WHERE row_number BETWEEN @start_row_num + 1 AND @start_row_num + @rows_per_page --執行分頁
END[/code]資料參考 [url]http://blog.blueshop.com.tw/blackncc/archive/2007/01/06/49123.aspx[/url] 這個方式會傳回多個結果集, 不過卻可以通用, 只要傳入 SQL 指令即可用 exec PagingRecordSet @strSQL='select A=1' 就可以了解
但是這樣 SQL 命令就需要自己處理 SQL Injection 的問題[code]
CREATE PROCEDURE PagingRecordSet
-- @strSQL VarChar(1000), --查詢sql,如select * from [user]
-- @strSQL NVarChar(1000), --查詢sql,如select * from [user]
@strSQL text, --查詢sql,如select * from [user]
@PageIndex int = 1, --查詢的頁號
@PageSize int = 30 --每頁顯示紀錄數
AS
set nocount on
declare @p1 int
declare @currentPage int
set @currentPage = 0
declare @RowCount int
set @RowCount = 0
declare @PageCount int
set @PageCount = 0
exec sp_cursoropen @p1 output, @strSQL, @scrollopt = 1, @ccopt = 1, @RowCount = @RowCount output --得到總紀錄數
select @PageCount = ceiling(1.0 * @RowCount / @PageSize) --得到總頁數
,@CurrentPage = (@PageIndex - 1) * @PageSize + 1
select @RowCount, @PageCount --顯示總紀錄數,總頁數
exec sp_cursorfetch @p1, 16, @CurrentPage, @PageSize
exec sp_cursorclose @p1
set nocount off
GO
[/code]此方式簡化了程式的複雜度, 但在巨量資料環境下效能就差很多
頁:
[1]