mysql - 当有一个包含更新数据的表时在 MySql 中查找重复项

Question

我知道我的问题已在许多不同的场合得到回答，但我无法让它发挥作用..

我正在尝试获取所有具有重复标识符的项目 ID，同时考虑项目是否已更新。

假设这是保存项目数据的主表

========================== item_table =========================
+----------+---------------+---------+------------+-----------+
| store_id | store_item_id | item_id | identifier | update_id |
+----------+---------------+---------+------------+-----------+
|1         | 1:1           | 1       | abc1       | 1         |
|1         | 1:2           | 2       | abc2       | NULL      |
|1         | 1:3           | 3       | abc3       | NULL      |
|1         | 1:4           | 4       | abc1       | 2         |
|1         | 1:5           | 5       | abc1       | NULL      |
|1         | 1:6           | 6       | NULL       | 3         |
|1         | 1:7           | 7       |            | NULL      |
|1         | 1:8           | 8       | abc1       | NULL      |
|2         | 2:9           | 9       | abc1       | NULL      |
+----------+---------------+---------+------------+-----------+

以及已更新项目的更新表(其中 id 匹配来自 update_id 的 item_table)

更新表会覆盖项目数据，如果已定义，它将被用来代替原始项目数据

===== update_table =====
+---------+------------+
| id      | identifier |
+---------+------------+
| 1       | abc0       |
| 2       | abc4       |
| 3       | abc1       |
+---------+------------+

约束

• 只检查是否identifier不是 NULL 或空字符串 ''
• 仅检查特定的 store_id
• 只选择item_id相同的地方 identifier出现两次以上(即 i.store_item_id <> g.store_item_id 检查是否是同一项目)

我有以下查询，它使用上述约束检查原始项目数据，但我也无法让它检查更新后的值

SELECT 
  g.item_id
FROM 
  item_table g
WHERE g.store_id = 1 AND EXISTS (
  SELECT 
    i.identifier 
  FROM 
    item_table i 
  WHERE 
    i.identifier = g.identifier AND 
    i.identifier <> '' AND 
    i.identifier IS NOT NULL AND
    i.store_item_id <> g.store_item_id AND
    i.store_id = 1
)

结果:

+---------+
| item_id |
+---------+
| 1       |
| 4       |
| 5       |
| 8       |
+---------+

但我需要检查更新表，因为如果定义了，那将是最终数据，我的查询应该返回的是

预期结果:

我需要所有 item_id在哪里store_id是 1 并且具有完全相同的标识符(即 abc1 )检查 update_table还有

+---------+
| item_id |
+---------+
| 5       |
| 6       |
| 8       |
+---------+

我已经尝试了各种方法让它工作，例如 JOINS , IFNULL , 其他使用方式 EXISTS或使用 GROUP BY与 HAVING ，但主要是我无法让它工作，因为 update_id并不总是被定义。有什么想法吗？

编辑/澄清

• duplicates在我的例子中意味着每个项目的标识符必须是唯一的。这并不意味着它不能插入数据库，我只是收集问题并将它们展示给用户，以便他们可以更新和修复数据
• 用户可以根据需要多次更新项目，但新值将在更新表中被覆盖，而不是创建新条目

Answer 1

SQL DEMO

SELECT item_id
FROM (
        SELECT store_id, item_id, COALESCE(u.`identifier`, i.`identifier`) as `identifier`
        FROM item_table i
        LEFT JOIN update_table u
           ON i.`update_id` = u.`id`
     ) as updated     
JOIN (
        SELECT store_id, COALESCE(u.`identifier`, i.`identifier`) as `identifier`, COUNT(*)
        FROM item_table i
        LEFT JOIN update_table u
           ON i.`update_id` = u.`id` 
        GROUP BY store_id, COALESCE(u.`identifier`, i.`identifier`) 
        HAVING COUNT(*) > 1
     ) as duplicated
  ON updated.`store_id` = duplicated.`store_id`
 AND updated.`identifier` = duplicated.`identifier`

WHERE updated.`identifier` <> ''
  AND updated.`identifier` IS NOT NULL

ORDER BY  item_id;

输出