盤點現在用的SqlServer 5種分頁方式和拉姆達表示式分頁，進來看看吧。

學習中的苦與樂發表於2022-12-15
SQLServer

現在基本上大家都在使用各種輪子自帶的分頁，大家是否還記得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、透過升序與降序方式進行查詢分頁（不推薦）

示例：

--使用升序降序的方式分頁查詢
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)函式（不推薦）

示例：

--MIN()函式和MAX()函式的使用
--id > 第(PageIndex-1)*PageSize條記錄的id AND id <= 第PageIndex*PageSize條記錄的id
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）

6、Lambda表示式分頁（推薦使用）

我們在資料庫分頁的時候，還可以在程式碼裡面使用lambda表示式分頁。

示例：

List<int> list = new List<int>();
for (int i = 0; i < 100; i++)
{
    list.Add(i);
}
//從第11條資料開始，獲取10條資料
list = list.Skip(11).Take(10).ToList(); //返回值 11，12,13,14,15，16,17,18,19,20

Skip： 表示從第 （pageIndex * pageSize + 1） 條資料開始，也就是說再這之前有pageIndex * pageSize條資料。

Task：表示獲取多少條資料。

通用用法

list = list.Skip(pageIndex * pageSize +1 ).Take(pageSize).ToList();

以上就是資料查詢中經常用到的方式，

在資料庫版本支援的情況下個人推薦程度排序：offset fetch netct  > lambda  > ROW_NUMBER() OVER() ，後面的就不推薦使用 。

這樣就可以配合儲存過程進行分頁了。

 

喜歡就點贊加關注。

歡迎關注訂閱微信公眾號【熊澤有話說】，更多好玩易學知識等你來取
作者：熊澤-學習中的苦與樂
公眾號：熊澤有話說
QQ群：711838388
出處：https://www.cnblogs.com/xiongze520/p/16985440.html
您可以隨意轉載、摘錄，但請在文章內註明作者和原文連結。  

 

 

相關文章