SQL Server下7種“數據分頁”方案全網最新最全
目錄
- 1、ROW_NUMBER() OVER()方式（SQL2012以下推薦使用）
- 2、offset fetch next方式（SQL2012及以上的版本才支持：推薦使用 ）
- 3、top not in方式 （不推薦）
- 4、通過升序與降序方式進行查詢分頁（不推薦）
- 5、采用MAX(ID)或者MIN(ID)函數（不推薦）
- 6、不依賴排序/排序Id的終極方案
- Sql Server常見的幾種分頁方式
數據分頁往往有三種常用方案。
第一種，把數據庫中存放的相關數據，全部讀入PHP/Java/C#代碼/內存，再由代碼對其進行分頁操作（速度慢，簡易性高）。
第二種，直接在數據庫中對相關數據進行分頁操作，再把分頁后的數據輸出給代碼程序（速度中，簡易性中）。
第三種，先把數據庫中的相關數據全部讀入“緩存”或第三方工具，再由代碼程序對“緩存”或第三方工具中的數據進行讀取+分頁操作（速度快，簡易性差）。
本文下面重點闡述上述【第二種】方案在SQL Server上的使用（其它種類數據庫由于Sql語句略有差異，所以需要調整，但方案也類似）
1、ROW_NUMBER() OVER()方式（SQL2012以下推薦使用）
示例：
SELECT * FROM
(SELECT ROW_NUMBER() OVER(ORDER BY menuId) AS RowId,* FROM sys_menu ) AS r
WHERE RowId BETWEEN 1 AND 10
用子查詢新增一列行號（ROW_NUMBER）RowId查詢，比較高效的查詢方式，只有在SQL Server2005或更高版本才支持。
BETWEEN 1 AND 10是指查詢第1到第10條數據（閉區間），在這里面需要注意的是OVER的括號里面可以寫多個排序字段。
通用用法
--pageIndex 表示指定頁
--pageSize 表示每頁顯示的條數
SELECT * FROM
(SELECT ROW_NUMBER() OVER(ORDER BY 排序字段) AS RowId,* FROM 表名 ) AS r
WHERE RowId BETWEEN ((pageIndex-1)*pageSize + 1) AND (pageIndex * PageSize)
2、offset fetch next方式（SQL2012及以上的版本才支持：推薦使用 ）
示例：
--offset fetch next方式查詢，最高效的查詢方式，只有在SQL Server2012或更高版本才支持 SELECT * FROM sys_menu ORDER BY menuId offset 0 ROWS FETCH NEXT 10 ROWS ONLY
offset 是跳過多少行，
next是取接下來的多少行，
句式offset...rows fetch nect ..rows only，注意rows和末尾的only 不要寫漏掉了，并且這種方式必須要接著Order by XX 使用，不然會報錯。
通用用法
--pageIndex 表示指定頁 --pageSize 表示每頁顯示的條數 SELECT * FROM 表名 ORDER BY 排序字段 offset ((pageIndex - 1) * pageSize) ROWS FETCH NEXT pageSize ROWS ONLY
3、top not in方式 （不推薦）
示例：
--查詢第11-20條記錄 SELECT TOP 10 menuId, * FROM sys_menu WHERE menuId NOT IN (SELECT TOP 10 menuId FROM sys_menu)
這條語句的原理是先查詢1-10條記錄的ID，然后再查詢ID不屬于這1-10條記錄的ID，并且只需要10條記錄，因為每頁大小就是10，
這就是獲取到的第11-20條記錄，這是非常簡單的一種寫法。
另外IN語句與NOT IN語句類似，這是NOT IN的寫法，但是這種寫法數據量大的話效率太低。
通用用法
--pageIndex 表示指定頁 --pageSize 表示每頁顯示的條數 SELECT TOP pageSize menuId, * FROM sys_menu WHERE menuId NOT IN (SELECT TOP ((pageSize-1)*pageIndex) menuId FROM sys_menu)
4、通過升序與降序方式進行查詢分頁（不推薦）
示例：
--查詢第11-20條記錄
SELECT * FROM(
SELECT TOP 10 * FROM(
SELECT TOP 20 * FROM sys_menu ORDER BY menuId ASC)
AS TEMP1 ORDER BY menuId DESC)
AS TEMP2 ORDER BY menuId ASC
這條語句首先查詢前20條記錄，然后在倒序查詢前10條記錄（即倒數10條記錄），
這個時候就已經獲取到了11-20條記錄，但是他們的順序是倒序，所以最后又進行升序排序。
通用方法
--pageIndex 表示指定頁
--pageSize 表示每頁顯示的條數
SELECT * FROM(
SELECT TOP pageSize * FROM(
SELECT TOP ((pageIndex - 1) * pageSize +(pageSize*2)) * FROM sys_menu ORDER BY menuId ASC)
AS TEMP1 ORDER BY menuId DESC)
AS TEMP2 ORDER BY menuId ASC
5、采用MAX(ID)或者MIN(ID)函數（不推薦）
示例：
--查詢第11-20條記錄
SELECT TOP 10 * FROM sys_menu WHERE menuId>
(SELECT MAX(menuId) FROM(SELECT TOP 10 menuId FROM sys_menu ORDER BY menuId) AS TEMP1) --（第10條的id）
這個理解起來也簡單，先把第10條記錄的id找出來（當然這里面是直接使用MAX()進行查找，MIN()函數的用法也是類似的），
然后再對比取比第10條記錄的id大的前10條記錄即為我們需要的結果。
這里要注意開始時的邊界值調整。
通用用法
--pageIndex 表示指定頁
--pageSize 表示每頁顯示的條數
SELECT TOP pageSize * FROM sys_menu WHERE menuId>
(SELECT MAX(menuId) FROM(SELECT TOP ((PageIndex-1)*PageSize) menuId FROM sys_menu ORDER BY menuId) AS TEMP1) --（第10條的id）
上述1~5方案，再配合存儲過程，你就能制造出適合你自己的“分頁”輪子，日后反復使用。
但它們有一定自身局限性：方案1、2、5都需要依賴一個排序Id（這個Id要么是個排序列，要么是個主鍵）。方案3、4則效率太低，完全不推薦。
6、不依賴排序/排序Id的終極方案
此方案在DeveloperSharp框架中有提供（基于.Net/.Net Core/.Net Framework），方案被廣東省的多個公司/項目采用，得到了實戰驗證+穩定性。
【第一步】：從NuGet引用DeveloperSharp包。
【第二步】：創建一個用來與數據庫進行通信的“數據源類”（文本示例為：TestData.cs），內容如下：
using DeveloperSharp.Structure.Model;
using DeveloperSharp.Framework.QueryEngine;
namespace YZZ
{
[DataSource(DatabaseType.SQLServer, "Server=localhost;Database=Test;Uid=sa;Pwd=123")]
public class TestData : DeveloperSharp.Structure.Model.DataLayer
{
//類中沒有任何代碼
}
}
說 明 ：“數據源類”（文本示例為：TestData.cs）必 須 繼 承 自 DeveloperSharp.Structure.Model.DataLayer 類 ， 并 且 在 其 上 設 置DataSource屬 性 的 初 始 化 值 為“數據庫類型”及其“鏈接字符串”。
【第三步】：添加通過“數據源類”（TestData）調用其PagePartition方法進行數據分頁的代碼。注 意：核心代碼就一行而已?。?/p>
代碼如下：
using DeveloperSharp.Extension;//Table擴展所在的命名空間
-----------------------------
class Program
{
static void Main(string[] args)
{
TestData td = new TestData();
//分頁
var pp = td.PagePartition("select top 5000 * from t_Order where Id>10 order by Id desc", 20, 162);
List<Product> Products = pp.Table.ToList<Product>();
foreach (var P in Products)
{
Console.WriteLine(P.Name);
}
Console.ReadLine();
}
}
Product類代碼如下：
public class Product
{
public string Id { get; set; }
public string Name { get; set; }
public int Quantity { get; set; }
}
此處的PagePartition方法有兩個重載方法，其詳細功能說明如下：
PagePartition
聲明：public PagePiece PagePartition(string RecordSet, string Id, int PageSize, int PageIndex)
用途：分頁功能(有主鍵)
參數：（1）string RecordSet --需要分頁的記錄集，可以是表、視圖、或者SQL語句
（2）string Id --主鍵
（3）int PageSize --頁面大小
（4）int PageIndex --當前頁碼
返回：PagePiece --頁片實體
PagePartition
聲明：public PagePiece PagePartition(string RecordSet, int PageSize, int PageIndex)
用途：分頁功能(無主鍵)
參數：（1）string RecordSet -- 需要分頁的記錄集，可以是表、視圖、或者SQL語句
（2）int PageSize --頁面大小
（3）int PageIndex --當前頁碼
返回：PagePiece --頁片實體
注意：
（1） 當你需要分頁的數據表有“主鍵”字段時，使用“分頁功能(有主鍵)”。反之，使用“分頁功能(無主鍵)”。
（2） RecordSet是你需要分頁的“數據總集”的SQL語句。該SQL語句的形式豐富多樣，可以帶條件、排序、甚至還能是多表的聯合查詢、等。
（3） 此方法符合最開始的【第二種】方案，是在SQL Server內部進行的分頁操作。而且可以不依賴于排序/排序Id。
以上內容到此結束，下面介紹下Sql Server常見的幾種分頁方式
Sql Server常見的幾種分頁方式
⒈offset fetch next方式【SqlServer2012及以上版本支持】【推薦】
select * from T_User order by id offset 5 rows /*（頁數-1） * 條數 */ fetch next 5 rows only /* 條數 */
⒉row_number() over()方式【SqlServer2005以上版本支持】
select * from (select *,row_number() over(order by id) as orderId from T_User) as t where t.orderId between 11 and 15 /* （頁數-1）* 條數 + 1 */ /* 頁數 * 條數 */
⒊top not in方式【適用于SqlServer2012以下版本】
select top 5 * from T_User where id not in (select top 10 id from T_User) /* top 條數 */ /* top 條數 * 頁數 */
⒋max(主鍵)方式【本質上還是top方式，適用于SqlServer2012以下版本】
select top 5 * from T_User where id>= (select max(id) from (select top 6 id from T_User order by id asc) a) order by id; /*top 條數*/ /*top（頁數-1）* 條數 + 1*/
分析：在數據量較大時
top not in方式：查詢靠前的數據速度較快
ROW_NUMBER() OVER()方式：查詢靠后的數據速度比上一種較快
offset fetch next方式：速度穩定，優于前2種，但sql版本限制2012及以上才可使用
到此這篇關于SQL Server下7種“數據分頁”方案，全網最全的文章就介紹到這了,更多相關SQL Server數據分頁內容請搜索以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持！
相關文章：
網公網安備