SQL Server 使用 ROW_NUMBER、RANK、DENSE_RANK 為查詢結果加上流水號並合併多結果集

前言

SQL Server 2005+ 引入視窗函數，ROW_NUMBER()、RANK()、DENSE_RANK() 可為查詢結果產生序號。本文說明三者差異、語法，並示範如何利用序號欄位合併多個結果集，提升查詢彈性。

基本語法

SELECT 
    ROW_NUMBER() OVER (ORDER BY 欄位) AS RowIndex,
    欄位1, 欄位2
FROM 資料表

• OVER (ORDER BY ...)：定義排序依據，必填。
• 可加入 PARTITION BY 分組欄位 於 ORDER BY 之前，實現分組內排序。

三大函數差異對照

假設資料：Name 欄位值為 張三, 張三, 李四, 王五, 王五, 王五，依 Name 排序。

實務範例：合併多個查詢結果集

情境：需將「學生基本資料」與「學生成績」兩張表查詢結果，依學號合併為單一結果集回傳前端。

-- CTE 產生帶流水號的基本資料
WITH BaseData AS (
    SELECT 
        ROW_NUMBER() OVER (ORDER BY StudentID) AS RowIndex,
        StudentID, StudentName, ClassName
    FROM Students
),
-- CTE 產生帶流水號的成績資料
ScoreData AS (
    SELECT 
        ROW_NUMBER() OVER (ORDER BY StudentID) AS RowIndex,
        StudentID, Subject, Score
    FROM Scores
)
-- 以 RowIndex 為鍵 JOIN（前提：兩表排序欄位、筆數完全一致）
SELECT 
    b.StudentID, b.StudentName, b.ClassName,
    s.Subject, s.Score
FROM BaseData b
INNER JOIN ScoreData s ON b.RowIndex = s.RowIndex
ORDER BY b.RowIndex;

⚠️ 重要限制與替代方案

以 RowIndex JOIN 極度脆弱：若兩表排序不一致、筆數不同、或中間插入/刪除資料，Join 即會錯位。生產環境請改用主鍵 (StudentID) JOIN：

SELECT s.StudentID, s.StudentName, s.ClassName, sc.Subject, sc.Score
FROM Students s
LEFT JOIN Scores sc ON s.StudentID = sc.StudentID
ORDER BY s.StudentID;

RowIndex 適合單一結果集的分頁、匯出 Excel 序號、臨時顯示，不適合作為跨結果集的關聯鍵。

進階用法：分組內流水號

SELECT 
    ClassName,
    StudentName,
    ROW_NUMBER() OVER (PARTITION BY ClassName ORDER BY Score DESC) AS ClassRank
FROM Students
ORDER BY ClassName, ClassRank;

產生各班級內依成績排名的流水號。

效能提示

• 視窗函數需排序，大量資料時確保 OVER (ORDER BY ...) 欄位有索引。
• 避免在子查詢中對大量資料使用 ROW_NUMBER() 再外層篩選（如 WHERE RowIndex BETWEEN 10 AND 20），SQL Server 2012+ 請直接用 OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY。