MYSQL如何合并两张表去重得到指定格式的输出结果

核心实现逻辑 #

• 结果集由两部分拼接组成：第一部分全量保留Table A的所有原有记录，第二部分仅追加Table B中未匹配的记录
• 匹配规则：Table B的记录仅当ID_P和Name两个字段同时能在Table A中找到完全匹配的项时才排除，否则纳入结果
• 追加Table B的记录时，将Table B缺失的ID、IP_Type字段统一填充为NULL，和Table A的字段结构对齐

具体实现代码 #

直接查询获取合并结果 #

SELECT ID, ID_P, IP_Type, Name
FROM Table_A

UNION ALL

SELECT
  NULL AS ID,
  ID_P,
  NULL AS IP_Type,
  Name
FROM Table_B b
WHERE NOT EXISTS (
  SELECT 1
  FROM Table_A a
  WHERE a.ID_P = b.ID_P
    AND a.Name <=> b.Name
);

代码说明：

• 用UNION ALL拼接两部分结果，不会对全量结果做额外去重，性能更高，同时因为逻辑上Table A的原有记录和过滤后的Table B记录没有交叉重复，不会出现冗余数据；如果需要对Table A本身的重复记录做去重，可在第一部分的Table_A查询前添加DISTINCT关键字
• 用NOT EXISTS替代NOT IN，避免字段存在NULL值时出现结果集异常
• 用MySQL原生的<=>安全等于运算符，兼容空字符串、NULL值的匹配场景，不会出现空值比对失效的问题

落地为物理表Table C #

如果需要将合并结果持久化到物理表，根据Table C的存在状态选择对应写法：

• Table C已存在，直接写入数据

INSERT INTO Table_C (ID, ID_P, IP_Type, Name)
-- 替换为上面的UNION ALL查询语句

• Table C不存在，直接通过查询创建新表并写入数据

CREATE TABLE Table_C AS
-- 替换为上面的UNION ALL查询语句

基于现有测试数据的预期结果 #

对照给出的现有数据，合并后Table C共包含8条记录：

• 前5条为Table A的全量原有记录
• 后3条为Table B中未匹配的记录：
  • (ID=NULL, ID_P=Cat, IP_Type=NULL, Name=Gordon)
  • (ID=NULL, ID_P=Cat, IP_Type=NULL, Name=Olivia)
  • (ID=NULL, ID_P=Cat, IP_Type=NULL, Name=George)

注：Table B中(ID_P=Cat, Name=Henry)的记录和Table A中ID=1的记录完全匹配，会被过滤掉。

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