資料表 Sample 包含 DepartmentId、Name 兩欄,呈現多對多關係。需求:先依 DepartmentId 排序,再取 Name 去重複值,並保留排序後的相對順序。期望結果:A, C, B。

SELECT DISTINCT name FROM Sample ORDER BY DepartmentId;
錯誤:ORDER BY項目必須出現在SELECT DISTINCT清單中。
SELECT DISTINCT name, DepartmentId FROM Sample ORDER BY DepartmentId;
結果錯誤:輸出含重複 Name (不同 DepartmentId 視為不同列),不符去重需求。

SELECT DISTINCT a.Name
FROM (SELECT TOP 100 PERCENT name FROM Sample ORDER BY DepartmentId) a;
結果順序錯誤:輸出A, B, C而非期望的A, C, B。

執行計畫揭示:DISTINCT 內部會執行排序去重 (Sort Distinct),此排序**不可控制**、且會覆蓋外層 ORDER BY。ORM 工具 (如 Entity Framework) 甚至會自動捨棄子查詢中的 ORDER BY。

DepartmentId 排序Name 分區,按 DepartmentId 排序編號 (ROW_NUMBER() OVER (PARTITION BY Name ORDER BY DepartmentId))DepartmentId 排序輸出SELECT a.Name
FROM (
SELECT TOP 100 PERCENT
Name,
DepartmentId,
ROW_NUMBER() OVER (PARTITION BY Name ORDER BY DepartmentId) AS rn
FROM Sample
ORDER BY DepartmentId
) a
WHERE a.rn = 1
ORDER BY a.DepartmentId;

成功輸出 A, C, B,符合「先排序、再去重、保留相對順序」需求。
Segment、Sequence Project、Filter 運算子
| 寫法 | 語法 | 適用場景 | 效能 |
|---|---|---|---|
| ROW_NUMBER (本文方案) | 標準 SQL:2003,SQL 2005+ 支援 | 嚴格保留排序後首現順序 | 中 |
| GROUP BY + MIN/MAX | SELECT Name, MIN(DepartmentId) AS FirstDept FROM Sample GROUP BY Name ORDER BY FirstDept | 只需任一關聯 DepartmentId,不在乎完整順序 | 優 |
| 應用層去重 | 資料庫只回傳排序後完整集合,應用層遍歷去重 | 資料量小、邏輯複雜、需極致彈性 | 視網路/記憶體 |