如何在SQL Server查詢中實現高效分頁
經常寫SQL查詢腳本的朋友，很可能會遭遇分頁查詢的問題。在MSSQL2000及以前的版本中，分頁的寫法大多采用純Top嵌套方式，寫法比較復雜、而且效率并不理想。
從MSSQL2005開始，SQL Server提供了一個內置函數ROW_NUMBER，這是一個非常神奇的函數。
從MSSQL2012開始，SQL Server提供了offset方法進行分頁。使用offset startPage rows fetch next pageSize rows only 方式進行分頁。
今天我們就談談ROW_NUMBER和offset的語法和在分頁中的應用。
ROW_NUMBER實現對結果集的輸出進行編號。 具體來說，返回結果集分區內行的序列號，每個分區的***行從 1 開始。
ROW_NUMBER ( ) OVER ( [ PARTITION BY 字段 , ] order_by_clause )PARTITION BY:將 FROM 子句生成的結果集劃分為應用 ROW_NUMBER 函數的分區。 value_expression 指定對結果集進行分區所依據的列。 如果未指定 PARTITION BY，則此函數將查詢結果集的所有行視為單個組。order_by_clause: 子句可確定在特定分區中為行分配*** ROW_NUMBER 的順序。 order by 子句是必選項。返回值:bigint。結果集分區內行的序列號。offset的含義及語法定義offset是order by的子句，主要用來限定返回的行數，用來做分頁也是很合適的。只是從MSSQL2012才開始支持。語法結構如下:
- FETCH { NEXT } { integer_constant | fetch_row_count_expression } { ROWS } ONLY
fetch_row_count_expression 可以是變量、參數或常量標量子查詢。 在使用子查詢時，它無法引用在外部查詢范圍中定義的任何列。也就是說，它無法與外部查詢相關聯。
結合到分頁，語法語法:
- offset startPage rows fetch next pageSize rows only
其中起始頁面:startPage=(@page-1)*@rows，頁面大小:pageSize=@rows
演示數據準備為了說明方便，我們準備一些演示數據，這是一個簡單的業務銷售表，字段只有業務員、銷售區域和銷售額，如下:
- declare @sale table( FName nvarchar(50), FDistrict nvarchar(50), FAmount decimal(28,10) );
- insert into @sale values ('張三','北京',20000), ('張三','上海',50000), ('張三','深圳',40000), ('張三','廣州',30000), ('李四','北京',30000), ('李四','上海',50000), ('李四','深圳',40000), ('李四','廣州',10000), ('王二','北京',70000), ('王二','上海',10000), ('王二','深圳',60000), ('王二','廣州',20000), ('馬六','北京',80000), ('馬六','上海',20000), ('馬六','深圳',70000), ('馬六','廣州',60000)
準備演示數據
比如我們希望按照業務員+銷售區域排序后，每4條記錄一頁顯示，寫法如下:
- declare @pagesize int =4;--每頁記錄數
- declare @pagenum int =1;--第幾頁
- select v.* from (select row_number() over(order by FName,FDistrict) as FRowIndex,* from @sale) as v where v.FRowIndex between @pagesize*(@pagenum-1)+1 and @pagenum*@pagesize;
分頁查詢
ROW_NUMBER函數在SQL中屬于熱名稱(即剛定的名稱FRowIndex)，只可以出現在select子句中，需要放在子查詢中。也可以先對子查詢做好定義后面再直接引用，語法如下:
- declare @pagesize int =4;--每頁記錄數
- declare @pagenum int =1;--第幾頁
- with saledata as ( select row_number() over(order by FName,FDistrict) as FRowIndex,* from @sale )
- select * from saledata where FRowIndex between @pagesize*(@pagenum-1)+1 and @pagenum*@pagesize;
分頁查詢
ROW_NUMBER來做分頁查詢，經過反復應用測試，效率還是很高的。完整的測試腳本參看下圖:
重復的代碼部分不再贅述，在查詢時要注意，offset是Order By的子句，不能獨立存在。語法結構如下:
- select * from @sale order by FName,FDistrict offset (@pagenum-1)*@pagesize rows fetch next @pagesize rows only
返回的結果與使用row_number是一致的。完整的測試腳本參看下圖:
希望對您有所幫助!
