MSSQL 分頁處理

bestlong

分頁的技巧有兩種，一種是直接透過 T-SQL，另一種是透過 Store Procedure

假設 Northwind 有一個 Customer的 Table，你需要取回 41~50 筆的記錄，T-SQL語法該如何作呢？

T-SQL：

1. Select Top 10 CustomerID,CompanyName,ContactName,Country
   
2. from Customers
   
3. where CustomerID Not in (Select top 40 CustomerID from Customers order by Country,CustomerID)
   
4. Order by Country,CustomerID

複製代碼

Store Procedure：

1. CREATE PROCEDURE northwind_OrdersPaged
   
2. (
   
3.   @PageIndex int,
   
4.   @PageSize int
   
5. )
   
6. AS
   
7. BEGIN
   
8. DECLARE @PageLowerBound int
   
9. DECLARE @PageUpperBound int
   
10. DECLARE @RowsToReturn int
    
11. 
    
12. -- First set the rowcount
    
13. SET @RowsToReturn = @PageSize * (@PageIndex + 1)
    
14. SET ROWCOUNT @RowsToReturn
    
15. 
    
16. -- Set the page bounds
    
17. SET @PageLowerBound = @PageSize * @PageIndex
    
18. SET @PageUpperBound = @PageLowerBound + @PageSize + 1
    
19. 
    
20. -- Create a temp table to store the select results
    
21. CREATE TABLE #PageIndex
    
22. (
    
23.   IndexId int IDENTITY (1, 1) NOT NULL,
    
24.   OrderID int
    
25. )
    
26. 
    
27. -- Insert into the temp table
    
28. INSERT INTO #PageIndex (OrderID)
    
29. SELECT OrderID
    
30. FROM Orders
    
31. ORDER BY OrderID DESC
    
32. 
    
33. -- Return total count
    
34. SELECT COUNT(OrderID) FROM Orders
    
35. 
    
36. -- Return paged results
    
37. SELECT O.*
    
38. FROM Orders O, #PageIndex PageIndex
    
39. WHERE O.OrderID = PageIndex.OrderID
    
40.   AND PageIndex.IndexID > @PageLowerBound
    
41.   AND PageIndex.IndexID < @PageUpperBound
    
42. ORDER BY PageIndex.IndexID
    
43. END

複製代碼

資料參考 http://blog.sina.com.tw/4907/art ... 07&entryid=3921

bestlong

SQL Server 2000 Paging and Sorting Using ROWCOUNT and SQL_VARIANT
http://www.codeproject.com/KB/da ... KPagingSorting.aspx

1. CREATE PROCEDURE dbo.up_GetSortedSalesOrdersByPageUsingRowset
   
2.     @orderedOnStart datetime,
   
3.     @orderedOnEnd datetime,
   
4.     @pageNumber int,
   
5.     @pageSize int,
   
6.     @sortExpression varchar(100),
   
7.     @sortOrder varchar(4),
   
8.     @virtualCount int OUTPUT
   
9. AS
   
10. 
    
11.     /*
    
12.         Make sure that the page number is at least 1
    
13.     */
    
14.     IF @pageNumber < 1
    
15.     BEGIN
    
16.         SET @pageNumber = 1
    
17.     END
    
18. 
    
19.     SELECT
    
20.         @virtualCount = COUNT(*)
    
21.     FROM
    
22.         Sales.SalesOrderHeader Header
    
23.     WHERE
    
24.         Header.[OrderDate] >= @orderedOnStart
    
25.         AND Header.[OrderDate] < @orderedOnEnd
    
26. 
    
27.     DECLARE @lastKeyValue numeric(18,0)
    
28.     DECLARE @lastAscendingSortValue SQL_Variant
    
29.     DECLARE @lastDescendingSortValue SQL_Variant
    
30. 
    
31.     DECLARE @numberToIgnore int
    
32. 
    
33.     SET @numberToIgnore = (@pageNumber-1) * @pageSize
    
34. 
    
35.     IF @numberToIgnore > 0
    
36.     BEGIN
    
37.         /*
    
38.         Get the last available sort data and unique key
    
39.         value from the last page.
    
40.         */
    
41.         SET ROWCOUNT @numberToIgnore
    
42. 
    
43.         SELECT
    
44.             @lastKeyValue = [UniqueValue],
    
45.             @lastAscendingSortValue = [AscendingSort],
    
46.             @lastDescendingSortValue = [DescendingSort]
    
47.         FROM
    
48.         (
    
49.             SELECT
    
50.                 Header.[SalesOrderID] AS [UniqueValue],
    
51.                 CASE
    
52.                     WHEN
    
53.                         UPPER(@sortOrder) = 'DESC'
    
54.                         AND UPPER(@sortExpression) = 'CUSTOMERID'
    
55.                     THEN
    
56.                         CONVERT(SQL_Variant, [CustomerID])
    
57.                     WHEN
    
58.                         UPPER(@sortOrder) = 'DESC'
    
59.                         AND UPPER(@sortExpression) = 'TOTALDUE'
    
60.                     THEN
    
61.                         CONVERT(SQL_Variant, [TotalDue])
    
62.                     WHEN
    
63.                         UPPER(@sortOrder) = 'DESC'
    
64.                         AND UPPER(@sortExpression) = 'ORDERDATE'
    
65.                     THEN
    
66.                         CONVERT(SQL_Variant, [OrderDate])
    
67.                     ELSE
    
68.                         NULL
    
69.                 END AS [DescendingSort],
    
70.                 CASE
    
71.                     WHEN
    
72.                         UPPER(@sortOrder) = 'ASC'
    
73.                         AND UPPER(@sortExpression) = 'CUSTOMERID'
    
74.                     THEN
    
75.                         CONVERT(SQL_Variant, [CustomerID])
    
76.                     WHEN
    
77.                         UPPER(@sortOrder) = 'ASC'
    
78.                         AND UPPER(@sortExpression) = 'TOTALDUE'
    
79.                     THEN
    
80.                         CONVERT(SQL_Variant, [TotalDue])
    
81.                     WHEN
    
82.                         UPPER(@sortOrder) = 'ASC'
    
83.                         AND UPPER(@sortExpression) = 'ORDERDATE'
    
84.                     THEN
    
85.                         CONVERT(SQL_Variant, [OrderDate])
    
86.                     ELSE
    
87.                         NULL
    
88.                 END AS [AscendingSort]
    
89.             FROM
    
90.                 Sales.SalesOrderHeader Header
    
91.             WHERE
    
92.                 Header.[OrderDate] >= @orderedOnStart
    
93.                 AND Header.[OrderDate] < @orderedOnEnd
    
94.             ) AS Derived
    
95.         ORDER BY
    
96.             [AscendingSort] ASC,
    
97.             [DescendingSort] DESC,
    
98.             [UniqueValue] ASC
    
99.     END
    
100. 
     
101.     /*
     
102.     Select the first @pageSize records that come after the last sort
     
103.     data/unique value from the last page. If this is the first page,
     
104.     just get the first @pageSize records.
     
105.     */
     
106. 
     
107.     SET ROWCOUNT @pageSize
     
108. 
     
109.     SELECT
     
110.         [SalesOrderID],
     
111.         [OrderDate],
     
112.         [TotalDue],
     
113.         [CustomerID]
     
114.     FROM
     
115.     (
     
116.         SELECT
     
117.             [SalesOrderID],
     
118.             [OrderDate],
     
119.             [TotalDue],
     
120.             [CustomerID],
     
121.             [SalesOrderID] As [UniqueValue],
     
122.             CASE
     
123.                 WHEN
     
124.                     UPPER(@sortOrder) = 'DESC'
     
125.                     AND UPPER(@sortExpression) = 'CUSTOMERID'
     
126.                 THEN
     
127.                     CONVERT(SQL_Variant, [CustomerID])
     
128.                 WHEN
     
129.                     UPPER(@sortOrder) = 'DESC'
     
130.                     AND UPPER(@sortExpression) = 'TOTALDUE'
     
131.                 THEN
     
132.                     CONVERT(SQL_Variant, [TotalDue])
     
133.                 WHEN
     
134.                     UPPER(@sortOrder) = 'DESC'
     
135.                     AND UPPER(@sortExpression) = 'ORDERDATE'
     
136.                 THEN
     
137.                     CONVERT(SQL_Variant, [OrderDate])
     
138.                 ELSE
     
139.                     NULL
     
140.             END AS [DescendingSort],
     
141.             CASE
     
142.                 WHEN
     
143.                     UPPER(@sortOrder) = 'ASC'
     
144.                     AND UPPER(@sortExpression) = 'CUSTOMERID'
     
145.                 THEN
     
146.                     CONVERT(SQL_Variant, [CustomerID])
     
147.                 WHEN
     
148.                     UPPER(@sortOrder) = 'ASC'
     
149.                     AND UPPER(@sortExpression) = 'TOTALDUE'
     
150.                 THEN
     
151.                     CONVERT(SQL_Variant, [TotalDue])
     
152.                 WHEN
     
153.                     UPPER(@sortOrder) = 'ASC'
     
154.                     AND UPPER(@sortExpression) = 'ORDERDATE'
     
155.                 THEN
     
156.                     CONVERT(SQL_Variant, [OrderDate])
     
157.                 ELSE
     
158.                     NULL
     
159.             END AS [AscendingSort]
     
160.         FROM
     
161.             Sales.SalesOrderHeader Header
     
162.         WHERE
     
163.             Header.[OrderDate] >= @orderedOnStart
     
164.             AND Header.[OrderDate] < @orderedOnEnd
     
165.     ) Derived
     
166.     WHERE
     
167.     (
     
168.         @lastAscendingSortValue IS NULL
     
169.         AND @lastDescendingSortValue IS NULL
     
170.         AND @lastKeyValue IS NULL
     
171.     )
     
172.     OR
     
173.     (
     
174.         (@lastAscendingSortValue IS NOT NULL)
     
175.         AND
     
176.         (
     
177.             ([AscendingSort] > @lastAscendingSortValue)
     
178.             OR
     
179.             (
     
180.                 [AscendingSort] = @lastAscendingSortValue
     
181.                 AND [UniqueValue] > @lastKeyValue
     
182.             )
     
183.         )
     
184.     )
     
185.     OR
     
186.     (
     
187.         (@lastDescendingSortValue IS NOT NULL)
     
188.         AND
     
189.         (
     
190.             ([DescendingSort] < @lastDescendingSortValue)
     
191.             OR
     
192.             (
     
193.                 [DescendingSort] = @lastDescendingSortValue
     
194.                 AND [UniqueValue] > @lastKeyValue
     
195.             )
     
196.         )
     
197.     )
     
198.     ORDER BY
     
199.         [AscendingSort] ASC,
     
200.         [DescendingSort] DESC,
     
201.         [SalesOrderID] ASC
     
202. 
     
203.     SET ROWCOUNT 0
     
204. GO

複製代碼

bestlong

1. --有排序，又稱【夾擠式分頁查詢】。MS-SQL 2000，常見的分頁用語法。
   
2. SELECT * FROM
   
3. (
   
4.     SELECT TOP [每頁長度] * FROM
   
5.     (
   
6.         SELECT TOP [每頁長度*第幾頁] [欄位1, 欄位2, ...]
   
7.         FROM [資料表]
   
8.         WHERE [篩選條件]     
   
9.         ORDER BY [排序欄位] [DESC/ASC]
   
10.     )
    
11.     AS [資料表别名1]
    
12.     ORDER BY [排序欄位] [ASC/DESC]
    
13. )
    
14. AS [資料表别名2]
    
15. ORDER BY [排序欄位] [DESC/ASC]

複製代碼

1. --MS-SQL 2005以後才有RANK()語法
   
2. SELECT * FROM
   
3. (
   
4.     SELECT rank() OVER ( ORDER BY [排序欄位] [ASC/DESC] ) AS RankNumber, * FROM
   
5.     (
   
6.         SELECT [欄位1, 欄位2, ...]
   
7.         FROM [資料表]
   
8.         WHERE [篩選條件]     
   
9.     )
   
10.     AS [資料表别名1]
    
11. ) AS [資料表别名2]
    
12. WHERE RankNumber between [每頁長度*(第幾頁-1)] and [每頁長度*第幾頁]

複製代碼

資料參考 http://www.player.idv.tw/prog/index.php?title=SQL#MS-SQL

bestlong

1. ALTER PROCEDURE [dbo].[sp_page_test]
   
2. @rows_per_page int,
   
3. @current_page int
   
4. AS
   
5. DECLARE @start_row_num int
   
6. BEGIN
   
7. SET @start_row_num = (@current_page) * @rows_per_page --設定從第幾個record開始讀
   
8. WITH t AS
   
9. (
   
10. SELECT ROW_NUMBER() OVER(ORDER BY DOCUMENT_ID) AS row_number, DOCUMENT_ID, DOCUMENT_NAME
    
11. FROM DM_DOCUMENT
    
12. ) --加入ROW_NUMBER()模擬成另一個table
    
13. SELECT * FROM t
    
14. WHERE row_number BETWEEN @start_row_num + 1 AND @start_row_num + @rows_per_page --執行分頁
    
15. END

複製代碼

資料參考 http://blog.blueshop.com.tw/blac ... 07/01/06/49123.aspx

bestlong

這個方式會傳回多個結果集, 不過卻可以通用, 只要傳入 SQL 指令即可用 exec PagingRecordSet @strSQL='select A=1' 就可以了解
但是這樣 SQL 命令就需要自己處理 SQL Injection 的問題

1. CREATE PROCEDURE PagingRecordSet
   
2. --  @strSQL VarChar(1000),  --查詢sql,如select * from [user]
   
3. --  @strSQL NVarChar(1000),  --查詢sql,如select * from [user]
   
4.   @strSQL text,         --查詢sql,如select * from [user]
   
5.   @PageIndex int = 1,   --查詢的頁號
   
6.   @PageSize int = 30    --每頁顯示紀錄數
   
7. AS
   
8.   set nocount on
   
9.   declare @p1 int
   
10.   declare @currentPage int
    
11.   set @currentPage = 0
    
12.   declare @RowCount int
    
13.   set @RowCount = 0
    
14.   declare @PageCount int
    
15.   set @PageCount = 0
    
16.   
    
17.   exec sp_cursoropen @p1 output, @strSQL, @scrollopt = 1, @ccopt = 1, @RowCount = @RowCount output --得到總紀錄數
    
18.   select @PageCount = ceiling(1.0 * @RowCount / @PageSize) --得到總頁數
    
19.         ,@CurrentPage = (@PageIndex - 1) * @PageSize + 1   
    
20. 
    
21.   select @RowCount, @PageCount --顯示總紀錄數,總頁數
    
22.   exec sp_cursorfetch @p1, 16, @CurrentPage, @PageSize
    
23.   exec sp_cursorclose @p1
    
24. 
    
25.   set nocount off
    
26. GO

複製代碼

此方式簡化了程式的複雜度, 但在巨量資料環境下效能就差很多