星期三, 6月 23, 2010

[MS SQL] 分頁

整理一下分頁的範例寫法,方便以後查詢參考:


Reference:by 聖殿祭師
分頁的技巧有兩種,一種是直接透過T-SQL,另一種是透過Store Procedure,在這Post出來跟大家分享一下:
T-SQL:
假設Northwind有一個Customer的Table,你需要取回41~50筆的記錄,T-SQL語法該如何作呢?
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


Store Procedure:
出自MSDN Magazine,是別人的智慧
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

==============我是分隔線=================
SQL2005 新增函數Row_NUMBER 分頁法
參考ms msdn
--使用ROW_NUMBER()新增RowNumber資料欄位
USE AdventureWorks2008R2;
GO
WITH OrderedOrders AS
(
    SELECT SalesOrderID, OrderDate,
    ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber'
    FROM Sales.SalesOrderHeader 
) 
--指定取出介於50~60列索引的資料列
SELECT * 
FROM OrderedOrders 
WHERE RowNumber BETWEEN 50 AND 60;
 
透過上面的範例可以寫成一個簡單的SP
ALTER PROCEDURE [dbo].[LobbyImages]
 @CourseFaculties int,
 @pageIndex int,
 @pageSize int
AS
BEGIN
select * from(
(select Row_NUMBER() over (order by HomeWorkPefectL4ID )
 as rownum,* from HomeWorkPerfectL4) )as newPerfectL4
 WHERE rownum BETWEEN (@PageIndex-1) * @PageSize + 1 AND @PageIndex * @PageSize
END 
 
 ==============我是分隔線=================

Pager Control for ASP.NET

這篇也有提供寫法:

sql 2000

sql 2005

 

沒有留言:

張貼留言

留個話吧:)

其他你感興趣的文章

Related Posts with Thumbnails