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

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
;

在测试数据上，返回:

| id | foreign_key | A     | #A | B    | #B |+----+-------------+-------+----+------+----+|  1 |           1 | A1    |  1 | B1   |  1 ||  4 |           2 | A2,A3 |  2 | B2   |  1 ||  6 |           3 | NULL  |  0 | NULL |  0 ||  7 |           4 | A4    |  1 | B4   |  1 ||  8 |           5 | A5    |  1 | B5   |  1 || 10 |           6 | A6,A7 |  2 | B6   |  1 || 12 |           7 | NULL  |  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
;

查询需要运算符(operator)干预的行:

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 会很好)。