SQL Server 連續資料去重技巧：利用 ROW_NUMBER() 實現狀態連續重複刪除

問題場景

在 SQL Server 資料庫中，常遇到需刪除「連續重複」記錄的需求。例如：某欄位 status 若與上一筆資料相同，則視為重複；但若中間夾雜不同值，則不視為重複。這類「連續去重」無法用單純 DISTINCT 或 GROUP BY 解決。

解決思路：差分分組法

核心技巧是「序號差分」：先按 status 排序產生序號 rn1，再按原始順序產生序號 rn2，兩者相減得到 rn_diff。連續相同 status 的記錄，其 rn_diff 值相同，即可作為分組鍵。

具體步驟

1. 按 status 排序生成 rn1：
   SELECT ROW_NUMBER() OVER (ORDER BY status) AS rn1 FROM table_name
2. 按原始順序（如 id）生成 rn2：
   SELECT ROW_NUMBER() OVER (ORDER BY id) AS rn2 FROM table_name
3. 計算差分 rn_diff = rn1 - rn2：連續相同 status 的記錄，rn_diff 相同。
4. 以 rn_diff 分組，保留每組第一筆：
   SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY rn_diff ORDER BY id) AS rn3 FROM cte) t WHERE rn3 = 1

完整範例碼

WITH cte AS (
    SELECT 
        id,
        status,
        ROW_NUMBER() OVER (ORDER BY status) AS rn1,
        ROW_NUMBER() OVER (ORDER BY id) AS rn2
    FROM your_table
),
grp AS (
    SELECT 
        id,
        status,
        rn1 - rn2 AS rn_diff
    FROM cte
)
DELETE FROM your_table
WHERE id IN (
    SELECT id FROM (
        SELECT 
            id,
            ROW_NUMBER() OVER (PARTITION BY rn_diff ORDER BY id) AS rn3
        FROM grp
    ) t
    WHERE rn3 > 1
);

優勢說明

• 純 SQL 實現，無需游標或迴圈，效能佳。
• 邏輯清晰：利用「序號差分不變」特性識別連續區塊。
• 適用於任何需「連續重複去重」的場景（如日誌清理、感測器資料壓縮）。