查找一个字段中具有重复项的行之间的差异

Question

我即将使用以下方法从数据库中删除重复项

delete from table 
  where id not in (
    select min(id) 
      from table 
      group by foreign_key);

：但是，我想在以下条件下执行此操作：

• 如果任何重复行在 fieldA 或 fieldB 中具有值
  • 如果任何重复项具有 fieldA 或 fieldB 值，但每个字段中只有一个唯一值，请保留该值
  • 如果每一行中有多个唯一值，请报告此信息以及 id 和 foreign_key，以便可以手动修复该值。

默认情况下，fieldA 和 fieldB 为 NULL，但在某些情况下已在这些字段中输入了数据。

这是一些示例数据：

| id | foreign_key | fieldA | fieldB |
|----+-------------+--------+--------|
|  1 |           1 | NULL   | NULL   |
|  2 |           1 | A1     | B1     |
|  3 |           1 | NULL   | NULL   |
|  4 |           2 | A2     | B2     |
|  5 |           2 | A3     | B2     |
|  6 |           3 | NULL   | NULL   |
|  7 |           4 | A4     | B4     |
|  8 |           5 | A5     | NULL   |
|  9 |           5 | NULL   | B5     |
| 10 |           6 | A6     | B6     |
| 11 |           6 | A7     | B6     |
| 12 |           7 | NULL   | B7     |
| 13 |           7 | NULL   | B7     |

我想要保留的是：

| id | foreign_key | fieldA | fieldB |
|----+-------------+--------+--------|
|  2 |           1 | A1     | B1     |
|  4 |           2 | NULL   | B2     |
|  6 |           3 | NULL   | NULL   |
|  7 |           4 | A4     | B4     |
|  8 |           5 | A5     | B5     |
| 10 |           6 | NULL   | B6     |
| 12 |           7 | NULL   | B7     |

我希望返回此信息：

foreign_key 2 has two distinct values of fieldA: A2 and A3

Answer 1

我现在必须运行，但这里有一个查询：

SELECT id, foreign_key, 
    group_concat(DISTINCT fieldA) as A, count(DISTINCT fieldA) as `#A`,
    group_concat(DISTINCT fieldB) as B, count(DISTINCT fieldB) as `#B`
  FROM t1
  GROUP BY foreign_key
;

在测试数据上，返回：

|编号 |外键 |一个 | #A |乙| #B |
+----+-------------+--------+----+------+----+
| 1 | 1 | A1 | 1 | B1 | 1 |
| 4 | 2 | A2,A3 | 2 | B2 | 1 |
| 6 | 3 |空 | 0 |空| 0 |
| 7 | 4 | A4| 1 | B4 | 1 |
| 8 | 5 | A5| 1 | B5| 1 |
| 10 | 10 6 | A6,A7 | 2 | B6 | 1 |
| 12 | 12 7 |空| 0 | B7 | 1 |

查询要保留的行：

SELECT id, foreign_key, 
    group_concat(DISTINCT fieldA) as A, count(DISTINCT fieldA) as `#A`, 
    group_concat(DISTINCT fieldB) as B, count(DISTINCT fieldB) as `#B`
  FROM t1
  GROUP BY foreign_key
  HAVING `#A` < 2 AND `#B` < 2
;

查询需要操作员干预的行：

SELECT id, foreign_key, 
    group_concat(DISTINCT fieldA) as A, count(DISTINCT fieldA) as `#A`, 
    group_concat(DISTINCT fieldB) as B, count(DISTINCT fieldB) as `#B`
  FROM t1
  GROUP BY foreign_key
  HAVING `#A` >= 2 OR `#B` >= 2
;

GROUP_CONCAT 可能不合适，具体取决于列中存储的数据格式。然而，结合 #A 和 #B，您可以检测到它何时不合适，所以这应该不是一个大问题。它也可能对性能产生太大影响，但我想不出可以以相同方式使用的另一个聚合函数（GROUP_COALESCE 会很好）。