SQL Server 2005 引入 ANSI SQL:2003 標準的排序視窗函數,允許在不分組的情況下為結果集分配排名。四大函數差異在於「相同值如何編號」與「編號是否連續」。
CREATE TABLE test (iname NVARCHAR(10), iage INT);
INSERT INTO test VALUES ('A', 20), ('B', 20), ('C', 18), ('D', 22), ('E', 18);
依 ORDER BY 排序,每列分配 唯一、連續 的整數 (1,2,3...),相同值也給不同號。
SELECT
ROW_NUMBER() OVER (ORDER BY iname DESC) AS rowid,
iname
FROM test;
結果:A=1, B=2, C=3, D=4, E=5 (依 iname DESC 排序)
相同排序值給相同排名,下一名次 跳過被佔用的號碼 (1,2,2,4...)。
SELECT DISTINCT
RANK() OVER (ORDER BY iname DESC) AS rowid,
iname, iage
FROM test;
結果:若 iname 相同則同 rank,下一名次跳號。
相同值同排名,但下一名次 緊接著下一個整數 (1,2,2,3...)。
SELECT DISTINCT
DENSE_RANK() OVER (ORDER BY iname DESC) AS rowid,
iname
FROM test;
依排序將結果集平均分成 n 個桶,每列標示所屬桶號 (1~n)。
SELECT DISTINCT
NTILE(5) OVER (ORDER BY iname DESC) AS bucket,
iname, iage
FROM test
ORDER BY bucket;
結果:5 筆資料分 5 桶,每桶 1 筆,bucket 為 1~5。
| 函數 | 相同值編號 | 編號連續性 | 典型用途 |
|---|---|---|---|
| ROW_NUMBER | 不同 | 連續 | 分頁、去重保留第一筆、唯一序號 |
| RANK | 相同 | 不連續 (跳號) | 競賽排名 (並列第一,第三名無第二) |
| DENSE_RANK | 相同 | 連續 | 分級統計、薪資等級 |
| NTILE(n) | 視分布而定 | 連續桶號 | 四分位數、十分位數、資料分桶分析 |
加入 PARTITION BY 可在各分區內獨立排名:
-- 各部門內依薪資排名
SELECT
dept, name, salary,
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn_dept,
RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rk_dept
FROM employee;
ORDER BY 欄位有索引支援。ROW_NUMBER 常用於 高效分頁 (配合 CTE/派生表):
WITH cte AS (
SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS rn FROM big_table
)
SELECT * FROM cte WHERE rn BETWEEN 21 AND 30; -- 第 3 頁,每頁 10 筆
四大排序函數各有適用場景:ROW_NUMBER 為最通用的唯一序號;RANK/DENSE_RANK 用於語意排名;NTILE 用於統計分桶。善用 PARTITION BY 實現分組內排名,是報表與分析查詢的利器。