目錄

• 1、ROW_NUMBER() OVER()方式（SQL2012以下推薦使用）
• 2、offset fetch next方式（SQL2012及以上的版本才支持：推薦使用 ）
• 3、top not in方式 （不推薦）
• 4、通過(guò)升序與降序方式進(jìn)行查詢分頁(yè)（不推薦）
• 5、采用MAX(ID)或者M(jìn)IN(ID)函數(shù)（不推薦）
• 6、不依賴排序/排序Id的終極方案
• Sql Server常見(jiàn)的幾種分頁(yè)方式

數(shù)據(jù)分頁(yè)往往有三種常用方案。

第一種，把數(shù)據(jù)庫(kù)中存放的相關(guān)數(shù)據(jù)，全部讀入PHP/Java/C#代碼/內(nèi)存，再由代碼對(duì)其進(jìn)行分頁(yè)操作（速度慢，簡(jiǎn)易性高）。

第二種，直接在數(shù)據(jù)庫(kù)中對(duì)相關(guān)數(shù)據(jù)進(jìn)行分頁(yè)操作，再把分頁(yè)后的數(shù)據(jù)輸出給代碼程序（速度中，簡(jiǎn)易性中）。

第三種，先把數(shù)據(jù)庫(kù)中的相關(guān)數(shù)據(jù)全部讀入“緩存”或第三方工具，再由代碼程序?qū)?ldquo;緩存”或第三方工具中的數(shù)據(jù)進(jìn)行讀取+分頁(yè)操作（速度快，簡(jiǎn)易性差）。

本文下面重點(diǎn)闡述上述【第二種】方案在SQL Server上的使用（其它種類數(shù)據(jù)庫(kù)由于Sql語(yǔ)句略有差異，所以需要調(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

用子查詢新增一列行號(hào)（ROW_NUMBER）RowId查詢，比較高效的查詢方式，只有在SQL Server2005或更高版本才支持。

BETWEEN 1 AND 10是指查詢第1到第10條數(shù)據(jù)（閉區(qū)間），在這里面需要注意的是OVER的括號(hào)里面可以寫(xiě)多個(gè)排序字段。

通用用法

--pageIndex 表示指定頁(yè)
--pageSize  表示每頁(yè)顯示的條數(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 是跳過(guò)多少行，

next是取接下來(lái)的多少行，

句式offset...rows fetch nect ..rows only，注意rows和末尾的only 不要寫(xiě)漏掉了，并且這種方式必須要接著Order by XX 使用，不然會(huì)報(bào)錯(cuò)。

通用用法

--pageIndex 表示指定頁(yè)
--pageSize  表示每頁(yè)顯示的條數(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)

這條語(yǔ)句的原理是先查詢1-10條記錄的ID，然后再查詢ID不屬于這1-10條記錄的ID，并且只需要10條記錄，因?yàn)槊宽?yè)大小就是10，

這就是獲取到的第11-20條記錄，這是非常簡(jiǎn)單的一種寫(xiě)法。

另外IN語(yǔ)句與NOT IN語(yǔ)句類似，這是NOT IN的寫(xiě)法，但是這種寫(xiě)法數(shù)據(jù)量大的話效率太低。

通用用法

--pageIndex 表示指定頁(yè)
--pageSize  表示每頁(yè)顯示的條數(shù)
SELECT TOP pageSize menuId, *
FROM sys_menu 
WHERE menuId NOT IN (SELECT TOP ((pageSize-1)*pageIndex) menuId FROM sys_menu)

4、通過(guò)升序與降序方式進(jìn)行查詢分頁(yè)（不推薦）

示例：

--查詢第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

這條語(yǔ)句首先查詢前20條記錄，然后在倒序查詢前10條記錄（即倒數(shù)10條記錄），

這個(gè)時(shí)候就已經(jīng)獲取到了11-20條記錄，但是他們的順序是倒序，所以最后又進(jìn)行升序排序。

通用方法

--pageIndex 表示指定頁(yè)
--pageSize  表示每頁(yè)顯示的條數(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）

這個(gè)理解起來(lái)也簡(jiǎn)單，先把第10條記錄的id找出來(lái)（當(dāng)然這里面是直接使用MAX()進(jìn)行查找，MIN()函數(shù)的用法也是類似的），

然后再對(duì)比取比第10條記錄的id大的前10條記錄即為我們需要的結(jié)果。

這里要注意開(kāi)始時(shí)的邊界值調(diào)整。

通用用法

--pageIndex 表示指定頁(yè)
--pageSize  表示每頁(yè)顯示的條數(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方案，再配合存儲(chǔ)過(guò)程，你就能制造出適合你自己的“分頁(yè)”輪子，日后反復(fù)使用。

但它們有一定自身局限性：方案1、2、5都需要依賴一個(gè)排序Id（這個(gè)Id要么是個(gè)排序列，要么是個(gè)主鍵）。方案3、4則效率太低，完全不推薦。

6、不依賴排序/排序Id的終極方案

此方案在DeveloperSharp框架中有提供（基于.Net/.Net Core/.Net Framework），方案被廣東省的多個(gè)公司/項(xiàng)目采用，得到了實(shí)戰(zhàn)驗(yàn)證+穩(wěn)定性。

【第一步】：從NuGet引用DeveloperSharp包。

【第二步】：創(chuàng)建一個(gè)用來(lái)與數(shù)據(jù)庫(kù)進(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
    {
//類中沒(méi)有任何代碼
    }
}

說(shuō) 明 ：“數(shù)據(jù)源類”（文本示例為：TestData.cs）必 須 繼 承 自 DeveloperSharp.Structure.Model.DataLayer 類 ， 并 且 在 其 上 設(shè) 置DataSource屬 性 的 初 始 化 值 為“數(shù)據(jù)庫(kù)類型”及其“鏈接字符串”。

【第三步】：添加通過(guò)“數(shù)據(jù)源類”（TestData）調(diào)用其PagePartition方法進(jìn)行數(shù)據(jù)分頁(yè)的代碼。注 意：核心代碼就一行而已！！

代碼如下：

using DeveloperSharp.Extension;//Table擴(kuò)展所在的命名空間
-----------------------------
    class Program
    {
static void Main(string[] args)
{
    TestData td = new TestData();

    //分頁(yè)
    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方法有兩個(gè)重載方法，其詳細(xì)功能說(shuō)明如下：

PagePartition
聲明：public PagePiece PagePartition(string RecordSet, string Id, int PageSize, int PageIndex)
用途：分頁(yè)功能(有主鍵)
參數(shù)：（1）string RecordSet     --需要分頁(yè)的記錄集，可以是表、視圖、或者SQL語(yǔ)句
（2）string Id     --主鍵
（3）int PageSize     --頁(yè)面大小
（4）int PageIndex     --當(dāng)前頁(yè)碼
返回：PagePiece  --頁(yè)片實(shí)體

PagePartition
聲明：public PagePiece PagePartition(string RecordSet, int PageSize, int PageIndex)
用途：分頁(yè)功能(無(wú)主鍵)
參數(shù)：（1）string RecordSet     -- 需要分頁(yè)的記錄集，可以是表、視圖、或者SQL語(yǔ)句
     （2）int PageSize    --頁(yè)面大小
（3）int PageIndex    --當(dāng)前頁(yè)碼
返回：PagePiece  --頁(yè)片實(shí)體

注意：

（1） 當(dāng)你需要分頁(yè)的數(shù)據(jù)表有“主鍵”字段時(shí)，使用“分頁(yè)功能(有主鍵)”。反之，使用“分頁(yè)功能(無(wú)主鍵)”。

（2） RecordSet是你需要分頁(yè)的“數(shù)據(jù)總集”的SQL語(yǔ)句。該SQL語(yǔ)句的形式豐富多樣，可以帶條件、排序、甚至還能是多表的聯(lián)合查詢、等。

（3） 此方法符合最開(kāi)始的【第二種】方案，是在SQL Server內(nèi)部進(jìn)行的分頁(yè)操作。而且可以不依賴于排序/排序Id。

以上內(nèi)容到此結(jié)束，下面介紹下Sql Server常見(jiàn)的幾種分頁(yè)方式

Sql Server常見(jiàn)的幾種分頁(yè)方式

⒈offset fetch next方式【SqlServer2012及以上版本支持】【推薦】

 select * from T_User
 order by id
 offset 5 rows    /*（頁(yè)數(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
 /* （頁(yè)數(shù)-1）* 條數(shù) + 1 */
 /* 頁(yè)數(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ù) * 頁(yè)數(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（頁(yè)數(shù)-1）* 條數(shù) + 1*/

分析：在數(shù)據(jù)量較大時(shí)

　　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ù)分頁(yè)”方案，全網(wǎng)最全的文章就介紹到這了,更多相關(guān)SQL Server數(shù)據(jù)分頁(yè)內(nèi)容請(qǐng)搜索以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持！