T-SQL 進階功能精講：Ranking 函數、CTE 遞歸、MERGE Upsert 全攻略

一、Ranking 視窗函數

SQL Server 2005 引入四大排名函數，皆需搭配 OVER (ORDER BY ...) 使用，可加 PARTITION BY 分組。

測試資料

CREATE TABLE [Test] (
    StudentID BIGINT NOT NULL,
    ClassID   BIGINT NOT NULL,
    TestScore DECIMAL(4,1) NOT NULL
);
INSERT INTO [Test] VALUES
(100001,100,90), (100002,100,85.5), (100003,100,80), (100004,100,80),
(100005,100,74), (101001,101,94), (101002,101,85.5), (101003,101,85.5);

四函數對比

SELECT *,
    ROW_NUMBER()  OVER (ORDER BY TestScore DESC) AS RN,
    RANK()        OVER (ORDER BY TestScore DESC) AS R,
    DENSE_RANK()  OVER (ORDER BY TestScore DESC) AS DR,
    NTILE(3)      OVER (ORDER BY TestScore DESC) AS N3
FROM [Test];

| 函數 | 行為 | 範例結果 (前 6 列) | |------|------|-------------------| | ROW_NUMBER | 唯一遞增序號 | 1, 2, 3, 4, 5, 6 | | RANK | 同分同名次，下一名次跳過 | 1, 2, 3, 3, 5, 6 | | DENSE_RANK | 同分同名次，下一名次不跳過 | 1, 2, 3, 3, 4, 5 | | NTILE(3) | 等分成 3 組，依序輪流分配 | 1, 1, 2, 2, 3, 3 |

分組排名

SELECT *,
    RANK() OVER (PARTITION BY ClassID ORDER BY TestScore DESC) AS ClassRank
FROM [Test];

分頁應用

WITH Ranked AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY TestScore DESC) AS RN
    FROM [Test]
)
SELECT * FROM Ranked WHERE RN BETWEEN 6 AND 10;

---

二、CTE (Common Table Expression)

基本語法

WITH cte_name (col1, col2) AS (
    SELECT col1, col2 FROM SomeTable
)
SELECT * FROM cte_name;

簡化巢狀查詢：取得各班第一名

WITH Ranked AS (
    SELECT StudentID, ClassID, TestScore,
           RANK() OVER (PARTITION BY ClassID ORDER BY TestScore DESC) AS RN
    FROM [Test]
)
SELECT * FROM Ranked WHERE RN = 1;

遞歸 CTE：查詢組織架構樹

CREATE TABLE Dept (
    DeptID BIGINT PRIMARY KEY,
    DeptName NVARCHAR(50),
    ParentDeptID BIGINT
);
INSERT INTO Dept VALUES
(1,N'集團',0), (2,N'總公司',1), (3,N'技術部',2), (4,N'開發組',3),
(5,N'測試部',3), (6,N'財務部',2), (7,N'分公司',1), (8,N'工程部',7),
(9,N'設備組',8), (10,N'客服部',7);

-- 取得「總公司」及其所有下層部門
WITH DeptTree AS (
    SELECT DeptID, DeptName, ParentDeptID FROM Dept WHERE DeptName = N'總公司'
    UNION ALL
    SELECT d.DeptID, d.DeptName, d.ParentDeptID
    FROM Dept d
    INNER JOIN DeptTree dt ON d.ParentDeptID = dt.DeptID
)
SELECT * FROM DeptTree;

遞歸 CTE 由錨點成員（初始查詢）與遞歸成員（自我聯結）組成，以 UNION ALL 串接，預設最大遞歸 100 層，可用 OPTION (MAXRECURSION n) 調整。

---

三、MERGE (Upsert) - SQL Server 2008+

語法結構

MERGE INTO TargetTable AS T
USING SourceTable AS S
ON T.KeyCol = S.KeyCol
WHEN MATCHED THEN
    UPDATE SET T.Col1 = S.Col1, T.Col2 = S.Col2
WHEN NOT MATCHED BY TARGET THEN
    INSERT (KeyCol, Col1, Col2) VALUES (S.KeyCol, S.Col1, S.Col2)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE
OUTPUT $action, INSERTED.*, DELETED.*;

實戰範例：以 NewTest 同步更新 Test

MERGE Test AS T
USING NewTest AS S
ON T.StudentID = S.StudentID
WHEN MATCHED THEN
    UPDATE SET T.TestScore = S.TestScore
WHEN NOT MATCHED BY TARGET THEN
    INSERT (StudentID, ClassID, TestScore)
    VALUES (S.StudentID, S.StudentID/1000, S.TestScore)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE
OUTPUT $action, INSERTED.*, DELETED.*;

OUTPUT 子句可同步取得異動明細（INSERT/UPDATE/DELETE），便於稽核或後續處理。

---

總結比較

功能	引入版本	核心優勢
Ranking 函數	2005	單次掃描完成排序、分組排名、分頁、分桶
CTE	2005	可讀性高、支援遞歸、替代暫存表/表變數
MERGE	2008	單一陳述式完成 Upsert、原子性、可 OUTPUT

善用這三大功能，能將大量程序式邏輯轉為集合式 SQL，大幅提升可維護性與效能。