刪除具有特殊規則的重複記錄 - 是否可以使用單個查詢？

我在表中有以下數據：

id    loc1    loc2    zip_code
------------------------------
10    1       null    12345
10    null    1       null

11    1       null    null
11    null    1       43210

12    1       null    54321
12    null    1       87654

13    1       null    null
13    null    1       null

14    1       null    65432
15    1       null    null
16    null    1       76767
17    null    1       null

目標是使用一些特殊規則刪除重複項。

為此，我可以選擇直接從原始表中刪除“壞”記錄或將“好”記錄複製到新表中。

規則：

• 每條記錄總是有一個位置集。即我們要麼有loc1或loc2每一行。

• 唯一的行（id: 14-17）很好，因為它們是

• 對於重複的行 ( id: 10-13)，我只想選擇一行：

  • 有一個的行zip_code
  • 對於所有其他情況，我們必須始終選擇主要位置。IE。那個有loc1

去重後，最終的數據應該是這樣的：

id    loc1    loc2    zip_code
------------------------------
10    1       null    12345
11    null    1       43210
12    1       null    54321
13    1       null    null

14    1       null    65432
15    1       null    null
16    null    1       76767
17    null    1       null

WITH cte AS ( SELECT *,
                    ROW_NUMBER() OVER (PARTITION BY id ORDER BY zip_code IS NULL, loc1 IS NULL) rn
             FROM test )
SELECT id, loc1, loc2, zip_code
FROM cte
WHERE rn = 1
ORDER BY id;

---

如果我想用它來刪除重複項，我需要做的就是改變 WHERE rn = 2。這樣對嗎？– 高級

@advncd 不，MySQL 不支持可更新的 CTE。您必須將此查詢用作子查詢，該查詢選擇在從另一個源表副本中刪除時必須儲存的行。

DELETE t1.*
FROM test t1
JOIN (WITH cte AS ( SELECT *,
                          ROW_NUMBER() OVER (PARTITION BY id ORDER BY zip_code IS NULL, loc1 IS NULL) rn
                   FROM test )
     SELECT id, loc1, loc2, zip_code
     FROM cte
     WHERE rn > 1) t2 ON t1.id = t2.id
                     AND t1.loc1 <=> t2.loc1
                     AND t1.loc2 <=> t2.loc2
                     AND t1.zip_code <=> t2.zip_code;

小提琴

引用自：https://dba.stackexchange.com/questions/276417