SQL Server 去重查詢進階：DISTINCT 限制與分組/窗口函數最佳實踐

問題場景：單列去重但保留完整行

表 tName(ID, AA, BB) 中 ID 重複，需按 ID 去重並保留每組「第一行」完整數據。DISTINCT ID, AA, BB 只能去除完全相同的行，無法實現「按單列去重、保留其他列」。

方案對比與優化

方案 1：聚合函數 + GROUP BY（不保證行一致性）

SELECT ID, MAX(AA) AS AA, MAX(BB) AS BB
FROM tName
GROUP BY ID;

• ✅ 語法簡單，性能好（流聚合/哈希聚合）。
• ❌ AA、BB 可能來自不同源行，業務語義錯誤（如 ID=1 時 AA=4535 來自行2，BB=6564 來自行3）。

方案 2：臨時表 + IDENTITY 排序（兩階段，大數據量性能差）

SELECT IDENTITY(INT,1,1) AS TID, ID, AA, BB INTO #Tmp FROM tName;
SELECT t1.ID, t1.AA, t1.BB
FROM #Tmp t1
WHERE t1.TID IN (SELECT MIN(TID) FROM #Tmp GROUP BY ID);

• ✅ 邏輯正確，保留物理插入順序的第一行。
• ❌ 寫入臨時表、再掃描，IO 雙倍；大表易爆 tempdb。

方案 3：ROW_NUMBER() 窗口函數（單語句、最優）

WITH Ranked AS (
    SELECT ID, AA, BB,
           ROW_NUMBER() OVER (PARTITION BY ID ORDER BY (SELECT 0)) AS rn  -- 保留任意行
           -- ORDER BY CreateTime DESC  -- 保留最新行
           -- ORDER BY Priority DESC    -- 保留優先級最高行
    FROM tName
)
SELECT ID, AA, BB
FROM Ranked
WHERE rn = 1;

• ✅ 單掃描、單語句、無臨時對象、優化器可下推過濾。
• ✅ ORDER BY 靈活控制保留策略（首行/最新/優先級）。
• ✅ 索引 (ID, CreateTime DESC) 可支持 Stream Aggregate 避免排序。

方案 4：CROSS APPLY Top 1（舊版本兼容、索引友好）

SELECT d.ID, ca.AA, ca.BB
FROM (SELECT DISTINCT ID FROM tName) d
CROSS APPLY (
    SELECT TOP 1 AA, BB FROM tName t WHERE t.ID = d.ID ORDER BY (SELECT 0)
) ca;

• ✅ SQL Server 2000+ 支持，配合 INDEX(ID) 可 Index Seek + Top 極快。
• ✅ 無窗口函數開銷，適合極高併發 OLTP。

方案 5：TOP 1 WITH TIES + 排序（技巧性寫法）

SELECT TOP 1 WITH TIES ID, AA, BB
FROM tName
ORDER BY ROW_NUMBER() OVER (PARTITION BY ID ORDER BY (SELECT 0));

• ✅ 單語句，但可讀性一般，不推薦常規使用。

性能基準測試參考 (100 萬行、ID 基數 10 萬)

最佳實踐建議

1. 首選 ROW_NUMBER()：語義清晰、可維護、性能優、支持複雜保留策略。
2. 高併發 OLTP 微優化：CROSS APPLY + 覆蓋索引 (ID) INCLUDE (AA, BB)。
3. 避免 DISTINCT 誤用：代碼評審時標記「單列去重保留全行」模式，強制走窗口函數。
4. 大表分區：按時間分區表，窗口函數可利用 Partition Elimination 僅掃描活躍分區。

去重看似簡單，實則考驗對 SQL 語義與執行引擎的理解。窗口函數是現代 SQL 解決「分組取 Top N」的標準武器，掌握其分區、排序、框架子句，能優雅高效地解決 90% 的去重/取樣需求。