如何合并多张Participants表生成带来源标识的All_Participants汇总表

方案1：SQL一次性全量插入（适用于首次创建All_Participants表场景） #

直接用UNION ALL加分组聚合的方式实现，主流数据库通用写法如下：

INSERT INTO All_Participants (name, age, in_participants_1, in_participants_2)
SELECT 
  name,
  age,
  MAX(is_p1) AS in_participants_1,
  MAX(is_p2) AS in_participants_2
FROM (
  -- 标记来自Participants_1的数据
  SELECT name, age, 1 AS is_p1, 0 AS is_p2 FROM Participants_1
  UNION ALL
  -- 标记来自Participants_2的数据
  SELECT name, age, 0 AS is_p1, 1 AS is_p2 FROM Participants_2
) AS combined
GROUP BY name, age;

逻辑说明：

• 先给每个源表的数据新增临时标记字段，标记自身所属源表
• 用UNION ALL合并所有源表数据，不会自动去重，性能优于UNION
• 最后按name和age分组，取标记字段的最大值，即可得到每个用户在对应源表是否存在的标识

方案2：支持扩展更多源表的写法 #

如果后续还要新增Participants_3、Participants_4这类源表，用关联的方式更方便扩展：

-- 支持FULL OUTER JOIN的数据库（PostgreSQL、Oracle等）可以直接用这个写法
INSERT INTO All_Participants (name, age, in_participants_1, in_participants_2)
SELECT 
  COALESCE(p1.name, p2.name) AS name,
  COALESCE(p1.age, p2.age) AS age,
  CASE WHEN p1.name IS NOT NULL THEN 1 ELSE 0 END AS in_participants_1,
  CASE WHEN p2.name IS NOT NULL THEN 1 ELSE 0 END AS in_participants_2
FROM Participants_1 p1
FULL OUTER JOIN Participants_2 p2 
  ON p1.name = p2.name AND p1.age = p2.age;

如果使用不支持FULL OUTER JOIN的数据库（比如MySQL），可以用以下兼容写法：

INSERT INTO All_Participants (name, age, in_participants_1, in_participants_2)
SELECT 
  all_users.name,
  all_users.age,
  CASE WHEN p1.name IS NOT NULL THEN 1 ELSE 0 END AS in_participants_1,
  CASE WHEN p2.name IS NOT NULL THEN 1 ELSE 0 END AS in_participants_2
FROM (
  SELECT name, age FROM Participants_1
  UNION
  SELECT name, age FROM Participants_2
) AS all_users
LEFT JOIN Participants_1 p1 
  ON all_users.name = p1.name AND all_users.age = p1.age
LEFT JOIN Participants_2 p2
  ON all_users.name = p2.name AND all_users.age = p2.age;

方案3：增量更新场景（适用于All_Participants已存在，后续源表有新增数据要同步的场景） #

用UPSERT逻辑实现，前提是All_Participants表需要给name和age加联合唯一索引，以下是MySQL示例：

-- 同步Participants_1的新增数据
INSERT INTO All_Participants (name, age, in_participants_1, in_participants_2)
SELECT name, age, 1 AS in_participants_1, 0 AS in_participants_2 FROM Participants_1
-- 可加WHERE条件过滤增量数据，比如WHERE create_time > 上次同步时间
ON DUPLICATE KEY UPDATE in_participants_1 = 1;

-- 同步Participants_2的新增数据
INSERT INTO All_Participants (name, age, in_participants_1, in_participants_2)
SELECT name, age, 0 AS in_participants_1, 1 AS in_participants_2 FROM Participants_2
ON DUPLICATE KEY UPDATE in_participants_2 = 1;

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