SQL Server下7種“數(shù)據(jù)分頁”方案全網(wǎng)最新最全
目錄
- 1、ROW_NUMBER() OVER()方式（SQL2012以下推薦使用）
- 2、offset fetch next方式（SQL2012及以上的版本才支持：推薦使用 ）
- 3、top not in方式 （不推薦）
- 4、通過升序與降序方式進(jìn)行查詢分頁（不推薦）
- 5、采用MAX(ID)或者M(jìn)IN(ID)函數(shù)（不推薦）
- 6、不依賴排序/排序Id的終極方案
- Sql Server常見的幾種分頁方式
數(shù)據(jù)分頁往往有三種常用方案。
第一種，把數(shù)據(jù)庫中存放的相關(guān)數(shù)據(jù)，全部讀入PHP/Java/C#代碼/內(nèi)存，再由代碼對其進(jìn)行分頁操作（速度慢，簡易性高）。
第二種，直接在數(shù)據(jù)庫中對相關(guān)數(shù)據(jù)進(jìn)行分頁操作，再把分頁后的數(shù)據(jù)輸出給代碼程序（速度中，簡易性中）。
第三種，先把數(shù)據(jù)庫中的相關(guān)數(shù)據(jù)全部讀入“緩存”或第三方工具，再由代碼程序?qū)?ldquo;緩存”或第三方工具中的數(shù)據(jù)進(jìn)行讀取+分頁操作（速度快，簡易性差）。
本文下面重點(diǎn)闡述上述【第二種】方案在SQL Server上的使用（其它種類數(shù)據(jù)庫由于Sql語句略有差異，所以需要調(diào)整，但方案也類似）
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條數(shù)據(jù)（閉區(qū)間），在這里面需要注意的是OVER的括號里面可以寫多個排序字段。
通用用法
--pageIndex 表示指定頁
--pageSize 表示每頁顯示的條數(shù)
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 表示每頁顯示的條數(shù) 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條記錄，因?yàn)槊宽摯笮【褪?0，
這就是獲取到的第11-20條記錄，這是非常簡單的一種寫法。
另外IN語句與NOT IN語句類似，這是NOT IN的寫法，但是這種寫法數(shù)據(jù)量大的話效率太低。
通用用法
--pageIndex 表示指定頁 --pageSize 表示每頁顯示的條數(shù) SELECT TOP pageSize menuId, * FROM sys_menu WHERE menuId NOT IN (SELECT TOP ((pageSize-1)*pageIndex) menuId FROM sys_menu)
4、通過升序與降序方式進(jìn)行查詢分頁（不推薦）
示例：
--查詢第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條記錄（即倒數(shù)10條記錄），
這個時候就已經(jīng)獲取到了11-20條記錄，但是他們的順序是倒序，所以最后又進(jìn)行升序排序。
通用方法
--pageIndex 表示指定頁
--pageSize 表示每頁顯示的條數(shù)
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)或者M(jìn)IN(ID)函數(shù)（不推薦）
示例：
--查詢第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找出來（當(dāng)然這里面是直接使用MAX()進(jìn)行查找，MIN()函數(shù)的用法也是類似的），
然后再對比取比第10條記錄的id大的前10條記錄即為我們需要的結(jié)果。
這里要注意開始時的邊界值調(diào)整。
通用用法
--pageIndex 表示指定頁
--pageSize 表示每頁顯示的條數(shù)
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方案，再配合存儲過程，你就能制造出適合你自己的“分頁”輪子，日后反復(fù)使用。
但它們有一定自身局限性：方案1、2、5都需要依賴一個排序Id（這個Id要么是個排序列，要么是個主鍵）。方案3、4則效率太低，完全不推薦。
6、不依賴排序/排序Id的終極方案
此方案在DeveloperSharp框架中有提供（基于.Net/.Net Core/.Net Framework），方案被廣東省的多個公司/項目采用，得到了實(shí)戰(zhàn)驗(yàn)證+穩(wěn)定性。
【第一步】：從NuGet引用DeveloperSharp包。
【第二步】：創(chuàng)建一個用來與數(shù)據(jù)庫進(jìn)行通信的“數(shù)據(jù)源類”（文本示例為：TestData.cs），內(nèi)容如下：
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
{
//類中沒有任何代碼
}
}
說 明 ：“數(shù)據(jù)源類”（文本示例為：TestData.cs）必 須 繼 承 自 DeveloperSharp.Structure.Model.DataLayer 類 ， 并 且 在 其 上 設(shè) 置DataSource屬 性 的 初 始 化 值 為“數(shù)據(jù)庫類型”及其“鏈接字符串”。
【第三步】：添加通過“數(shù)據(jù)源類”（TestData）調(diào)用其PagePartition方法進(jìn)行數(shù)據(jù)分頁的代碼。注 意：核心代碼就一行而已！！
代碼如下：
using DeveloperSharp.Extension;//Table擴(kuò)展所在的命名空間
-----------------------------
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方法有兩個重載方法，其詳細(xì)功能說明如下：
PagePartition
聲明：public PagePiece PagePartition(string RecordSet, string Id, int PageSize, int PageIndex)
用途：分頁功能(有主鍵)
參數(shù)：（1）string RecordSet --需要分頁的記錄集，可以是表、視圖、或者SQL語句
（2）string Id --主鍵
（3）int PageSize --頁面大小
（4）int PageIndex --當(dāng)前頁碼
返回：PagePiece --頁片實(shí)體
PagePartition
聲明：public PagePiece PagePartition(string RecordSet, int PageSize, int PageIndex)
用途：分頁功能(無主鍵)
參數(shù)：（1）string RecordSet -- 需要分頁的記錄集，可以是表、視圖、或者SQL語句
（2）int PageSize --頁面大小
（3）int PageIndex --當(dāng)前頁碼
返回：PagePiece --頁片實(shí)體
注意：
（1） 當(dāng)你需要分頁的數(shù)據(jù)表有“主鍵”字段時，使用“分頁功能(有主鍵)”。反之，使用“分頁功能(無主鍵)”。
（2） RecordSet是你需要分頁的“數(shù)據(jù)總集”的SQL語句。該SQL語句的形式豐富多樣，可以帶條件、排序、甚至還能是多表的聯(lián)合查詢、等。
（3） 此方法符合最開始的【第二種】方案，是在SQL Server內(nèi)部進(jìn)行的分頁操作。而且可以不依賴于排序/排序Id。
以上內(nèi)容到此結(jié)束，下面介紹下Sql Server常見的幾種分頁方式
Sql Server常見的幾種分頁方式
⒈offset fetch next方式【SqlServer2012及以上版本支持】【推薦】
select * from T_User order by id offset 5 rows /*（頁數(shù)-1） * 條數(shù) */ fetch next 5 rows only /* 條數(shù) */
⒉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 /* （頁數(shù)-1）* 條數(shù) + 1 */ /* 頁數(shù) * 條數(shù) */
⒊top not in方式【適用于SqlServer2012以下版本】
select top 5 * from T_User where id not in (select top 10 id from T_User) /* top 條數(shù) */ /* top 條數(shù) * 頁數(shù) */
⒋max(主鍵)方式【本質(zhì)上還是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 條數(shù)*/ /*top（頁數(shù)-1）* 條數(shù) + 1*/
分析：在數(shù)據(jù)量較大時
top not in方式：查詢靠前的數(shù)據(jù)速度較快
ROW_NUMBER() OVER()方式：查詢靠后的數(shù)據(jù)速度比上一種較快
offset fetch next方式：速度穩(wěn)定，優(yōu)于前2種，但sql版本限制2012及以上才可使用
到此這篇關(guān)于SQL Server下7種“數(shù)據(jù)分頁”方案，全網(wǎng)最全的文章就介紹到這了,更多相關(guān)SQL Server數(shù)據(jù)分頁內(nèi)容請搜索以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持！
相關(guān)文章：
網(wǎng)公網(wǎng)安備