MariDB中正确筛选并插入缺失place=2的记录（解决重复数据问题）

首先，咱们先解决你遇到的重复数据问题——之前用EXCEPT的方式虽然能拿到缺失的code，但没法精准过滤掉已经存在对应place=2的行，导致重复插入。下面给你两种更可靠的方法，既能准确筛选出需要补充的记录，又能避免重复：

方法一：用NOT EXISTS精准筛选待插入记录 #

这种方法是逐行检查每个place=1的记录，确认它没有对应的place=2记录后再插入，从根源杜绝重复：

第一步：先验证待插入的记录（可选但推荐） #

先运行这个查询，看看哪些place=1的记录需要补充place=2的行：

SELECT *
FROM cityX c1
WHERE c1.place = 1
  AND NOT EXISTS (
    SELECT 1
    FROM cityX c2
    WHERE c2.code = c1.code
      AND c2.place = 2
      -- 如果你的需求是「每个place=1的完整行都要对应place=2的行」（不是只按code匹配），就加上下面的字段匹配条件
      -- AND c2.value = c1.value
      -- AND c2.description = c1.description
      -- AND c2.subcode = c1.subcode
  );

第二步：插入缺失的place=2行 #

确认查询结果正确后，直接用这个INSERT语句批量插入：

INSERT INTO cityX (code, place, value, description, subcode)
SELECT code, 2, value, description, subcode
FROM cityX c1
WHERE c1.place = 1
  AND NOT EXISTS (
    SELECT 1
    FROM cityX c2
    WHERE c2.code = c1.code
      AND c2.place = 2
      -- 同样，需要字段完全匹配的话就加上上面的注释条件
  );

因为你的id是自增主键，所以不需要在INSERT里指定id，数据库会自动生成新的id值。

方法二：用LEFT JOIN筛选（和NOT EXISTS效果一致） #

如果更习惯用JOIN的写法，也可以用LEFT JOIN来找出没有对应place=2的place=1记录：

-- 先验证
SELECT c1.*
FROM cityX c1
LEFT JOIN cityX c2 ON c1.code = c2.code AND c2.place = 2
WHERE c1.place = 1 AND c2.id IS NULL;

-- 插入
INSERT INTO cityX (code, place, value, description, subcode)
SELECT c1.code, 2, c1.value, c1.description, c1.subcode
FROM cityX c1
LEFT JOIN cityX c2 ON c1.code = c2.code AND c2.place = 2
WHERE c1.place = 1 AND c2.id IS NULL;

验证插入结果 #

最后可以运行这个查询确认是否所有code都满足place=1和place=2的行数一致：

SELECT 
  code,
  COUNT(CASE WHEN place = 1 THEN 1 END) AS place1_count,
  COUNT(CASE WHEN place = 2 THEN 1 END) AS place2_count
FROM cityX
GROUP BY code
HAVING place1_count != place2_count;

如果这个查询返回空结果，说明所有记录都符合要求了。

内容的提问来源于stack exchange，提问作者Backwater