SQL Server 2005快速Web分頁的實現
分頁,就是按照某種規則顯示分組資料集,但是在SQL Server中,分頁並不是十分容易就能夠實現。在過去,開發人員通常需要自己編寫程式,使用臨時表格來實現分頁功能,或者將所有的資料結果集返回到客戶端,在客戶端進行分頁操作。從開發人員或者DBA的角度來看,兩種方法都不能令人滿意。
隨著SQL Server的釋出,其中的一些排序函式使得開發人員編寫資料分頁程式變得更加簡單和高效。這些新的排序函式提供了統計資料集的數目,對資料集歸類,按照某種標準對資料集排序等功能。在這篇文章中,我將著重介紹新增加的ROW-NUMBER排序函式,它會根據你指定的分類標準將結果資料集進行分類,同時給資料集分配連續的頁面。
一個分頁的例項
我總是喜歡通過例子來介紹如何使用新技術,所以讓我們來看看如何設計一個儲存程式,使用ROW_NUMBER這一新函式來實現資料的自動分頁。
首先,需要定義一些資料結構。我們定義一個SalesHistory表格,它包含的資料是我們在網上售出產品的銷售記錄。包括一些常見的銷售資訊,例如,所售產品、售出日期、產品售出價格等。下面的指令碼就是建立這樣的一個表格:
以下為引用的內容:
IF OBJECT_ID('SalesHistory','U') > 0
DROP TABLE SalesHistory
CREATE TABLE SalesHistory
(
SaleID INT IDENTITY(1,1),
Product VARCHAR(30),
SaleDate SMALLDATETIME,
SalePrice MONEY
)
執行列表A中的指令碼則在上面建立的SalesHistory表中新增一些例子資料。
以下為引用的內容:
DECLARE @i SMALLINT
SET @i = 1
WHILE (@i <=100)
BEGIN
INSERT INTO SalesHistory
(Product, SaleDate, SalePrice)
VALUES
('Computer', DATEADD(mm, @i, '3/11/1919'),
DATEPART(ms, GETDATE()) + (@i + 57) )
INSERT INTO SalesHistory
(Product, SaleDate, SalePrice)
VALUES
('BigScreen', DATEADD(mm, @i, '3/11/1927'),
DATEPART(ms, GETDATE()) + (@i + 13) )
INSERT INTO SalesHistory
(Product, SaleDate, SalePrice)
VALUES
('PoolTable', DATEADD(mm, @i, '3/11/1908'),
DATEPART(ms, GETDATE()) + (@i + 29) )
SET @i = @i + 1
END
列表A
現在資料表中已經具有例項資料。接下來我們看看如何呼叫程式來實現資料的分頁顯示。列表B包含這個程式的指令碼內容。這個程式含有兩個引數:
1.頁面大小(給定頁面要顯示的資料記錄數目)。
2.目標頁面(返回該頁的資料記錄)。
以下為引用的內容:
CREATE PROCEDURE usp_SalesRecords
(
@PageSize FLOAT,
@TargetPage SMALLINT
)
AS
BEGIN
WITH Sales_CTE(PageNumber, SaleID, Product, SaleDate, SalePrice)
AS
(
SELECT
CEILING((ROW_NUMBER() OVER
ORDER BY SaleDate ASC))/@PageSize) AS PageNumber, SaleID,
Product, SaleDate, SalePrice
FROM SalesHistory FROM SalesHistory
)
SELECT
PageNumber, SaleID, Product, SaleDate, SalePrice
FROM
Sales_CTE
WHERE
PageNumber = @Targetpage
ENDCREATE PROCEDURE usp_SalesRecords
(
@PageSize FLOAT,
@TargetPage SMALLINT
)
AS
BEGIN
WITH Sales_CTE(PageNumber, SaleID, Product, SaleDate, SalePrice)
AS
(
SELECT
CEILING((ROW_NUMBER() OVER
(ORDER BY SaleDate ASC))/@PageSize) AS PageNumber, SaleID,
Product, SaleDate, SalePrice
FROM SalesHistory FROM SalesHistory
)
SELECT
PageNumber, SaleID, Product, SaleDate, SalePrice
FROM
Sales_CTE
WHERE
PageNumber = @Targetpage
END
列表B
如果你剛剛開始使用SQL Server,可能會不熟悉以“WITH”開頭的宣告語句。這條語句會呼叫SQL Server中的一個新屬性,我們稱之為common table expression(CTE),從本質上來說,我們可以將CTE看作是高版本的臨時表。
分頁的實質就是CTE中的TSQL語句。在下面的選擇語句中,我使用了一個新的排序函式——ROW_NUMBER(這一函式很容易使用,你只需要給ROW_NUMBER函式提供一個域名作為引數,ROW_NUMBER會用它來進行分頁)。隨後,我使用@PageSize引數來劃分每頁的行數以及每頁的最大行數值。
例如,假設現在有一個包含三條記錄的資料集,並設計每頁顯示兩條記錄,那麼頭兩條記錄將會在第一頁顯示,因為每頁的行數必須小於或者等於第一個變數值。第三條記錄將會在第二頁顯示,因為每頁的可顯示最大行數值應該小於2但是又大於1。
可以使用下面的指令碼呼叫儲存程式:
以下為引用的內容:
EXECUTE usp_SalesRecords
@PageSize = 3,
@TargetPage = 2
執行程式後的返回結果如下:
PageNumber
SaleID
Product
SaleDate
SalePrice
2
12
PoolTable
7/11/1908
0:00
640
2
15
PoolTable
8/11/1908
0:00
641
2
18
PoolTable
9/11/1908
0:00
658
就如你所看到的,程式執行後將會返回一頁的資料,包含三條記錄,而且返回的是第二頁的資料集。
注意:一般來說,有兩種方法完成資料結果的分頁:在資料庫層實現和不在資料庫層實現。可以在客戶端實現分頁,但是這樣做的時候,所有的資料都會返回到客戶端,而且在進行資料分析的時候就決定了頁面數目。在早期版本的SQL Server中,可以在資料庫層實現分頁,但是需要臨時表和表變數。如果上面的例子沒有使用CTE來進行分頁的話,分頁程式就不會那麼簡單。之所以這麼簡單就是因為使用了ROW_NUMBER函式的強大功能。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/16436858/viewspace-544962/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- [SQL Server]分頁功能的實現SQLServer
- SQL SERVER 2005分頁儲存過程SQLServer儲存過程
- Sql Server 2005 row_number()分頁效能測試SQLServer
- oracle資料庫用sql實現快速分頁Oracle資料庫SQL
- Java Web 分頁實現JavaWeb
- 分頁procedure (SQL Server)SQLServer
- 【清清月兒】用SQL 2005的ROW_NUMBER() 實現分頁功能SQL
- web 實現分頁列印功能Web
- Java Web(十一) 分頁功能的實現JavaWeb
- SQL Server 2005中的DDL觸發器的實現SQLServer觸發器
- 使用SQL Server 2005的新函式構造分頁儲存過程SQLServer函式儲存過程
- 在SQL Server 2005中實現表的行列轉換SQLServer
- SQL SERVER 2005映象實驗SQLServer
- 使用SQL Server2005的新函式構造分頁儲存過程SQLServer函式儲存過程
- SQL SERVER分頁演算法SQLServer演算法
- SQL Server2005使用CTE實現遞迴QCSQLServer遞迴
- MySql/Oracle和SQL Server的分頁查MySqlOracleServer
- (SQL Server)分頁的儲存過程SQLServer儲存過程
- SQL Server 2005預設區分大小寫SQLServer
- 實戰 SQL Server 2005 映象配置SQLServer
- SQL Server 2005實現資料庫快取依賴SQLServer資料庫快取
- SQL Server 2005 實現資料庫快取依賴的實現步驟整理SQLServer資料庫快取
- java web實現分頁顯示資料JavaWeb
- SQL Server 2005命令提示實用工具SQLServer
- SQL Server的分頁優化及Row_Number()分頁存在的問題SQLServer優化
- 分頁的實現
- SQL server 2005 expressSQLServerExpress
- SQL Server2005 實現資料庫快取依賴的實現步驟整理SQLServer資料庫快取
- Sql Server系列:查詢分頁語句SQLServer
- 快速搞定在Eclipse中用JDBC連線SQL Server 2005EclipseJDBCSQLServer
- SQL Server 2005中修改 Server Collation的方法SQLServer
- SQL Server 2005資料頁讀取--高階掃描SQLServer
- 在SQL Server 2005資料庫中實現自動備份SQLServer資料庫
- 支援DISTINCT的通用分頁儲存過程(SQL2005)儲存過程SQL
- Sql Server 2005函式SQLServer函式
- SQL SERVER 2005 配置-saSQLServer
- 分頁功能的實現
- SQL Server 2005鎖的問題SQLServer