您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> SQL Server 2005

SQL Server 2005 分頁查詢與排名函數進階應用（AD HOC PAGING、MULTIPAGE ACCESS、RANK、NTILE）

編輯：SQL Server 2005

一、 AD HOC PAGING：單頁獨立請求模式

適用於使用者隨機跳頁、資料量大、不需預先快取所有頁碼的情境。核心技巧：ROW_NUMBER() 配合 WHERE rownum BETWEEN @Start AND @End。

DECLARE @pagesize INT = 5, @pagenum INT = 2;
WITH SalesCTE AS (
    SELECT ROW_NUMBER() OVER (ORDER BY qty, empid) AS rownum,
           empid, mgrid, qty
    FROM dbo.Sales
)
SELECT rownum, empid, mgrid, qty
FROM SalesCTE
WHERE rownum > @pagesize * (@pagenum - 1)
  AND rownum <= @pagesize * @pagenum
ORDER BY rownum;

特性：

僅掃描目標頁之前的資料列（例：第 2 頁只掃前 10 列），後續資料完全不讀取。
每次請求獨立編譯執行計畫，適合低重複率的隨機存取。

二、 MULTIPAGE ACCESS：多頁快取模式

適用於資料集較小、使用者順序翻頁、高重複存取的情境。策略：一次性將所有資料列物化並加上 ROW_NUMBER，建立叢集索引，後續分頁直接走索引搜尋。

-- 1. 物化並建立索引
SELECT ROW_NUMBER() OVER (ORDER BY qty, empid) AS rownum,
       empid, mgrid, qty
INTO #SalesRN
FROM dbo.Sales;
CREATE UNIQUE CLUSTERED INDEX idx_rn ON #SalesRN(rownum);

-- 2. 後續分頁查詢
DECLARE @pagesize INT = 5, @pagenum INT = 2;
SELECT rownum, empid, mgrid, qty
FROM #SalesRN
WHERE rownum BETWEEN @pagesize * (@pagenum - 1) + 1 AND @pagesize * @pagenum
ORDER BY rownum;

優勢：首次載入付出全掃描成本，後續每頁僅需 邏輯讀取（記憶體）+ 少量物理讀取，延遲極低。

三、排名函數深度比較：ROW_NUMBER vs RANK vs DENSE_RANK

SELECT empid, qty,
       ROW_NUMBER() OVER (ORDER BY qty) AS rn,
       RANK()       OVER (ORDER BY qty) AS rk,
       DENSE_RANK() OVER (ORDER BY qty) AS drk
FROM dbo.Sales
ORDER BY qty;

函數	相同 ORDER BY 值的處理	編號是否連續	典型用途
ROW_NUMBER	強制依序編號（不確定性）	是	分頁、唯一序號
RANK	同分同名次，下一名次跳過間隙	否	競賽排名、並列處理
DENSE_RANK	同分同名次，下一名次緊接	是	分級統計、連續梯級

四、 NTILE：等寬分桶（直方圖、評等）

SELECT empid, qty,
       NTILE(3) OVER (ORDER BY qty, empid) AS bucket,
       CASE NTILE(3) OVER (ORDER BY qty, empid)
            WHEN 1 THEN 'Low'
            WHEN 2 THEN 'Medium'
            WHEN 3 THEN 'High'
       END AS level
FROM dbo.Sales
ORDER BY qty, empid;

分配演算法：總列數 ÷ 桶數 = 基礎大小，餘數 n 分配給前 n 個桶（每桶 +1）。確保桶大小差異至多 1 列，適合製作直方圖或將資料分為 High/Medium/Low 三級。