多表合并生成存在性哑变量标识列的最优实现方案

架构老林

2026-9-25

解决方案

方案1：通用UNION ALL + 分组聚合（推荐）

你原有的实现思路本身性能表现优秀，调整写法后完全可以做到简洁易读，兼容所有主流数据库：

SELECT 
  id,
  city,
  MAX(is_tbl_1) AS is_tbl_1,
  MAX(is_tbl_2) AS is_tbl_2,
  MAX(is_tbl_3) AS is_tbl_3
FROM (
  SELECT id, city, 1 AS is_tbl_1, 0 AS is_tbl_2, 0 AS is_tbl_3 FROM table1
  UNION ALL
  SELECT id, city, 0 AS is_tbl_1, 1 AS is_tbl_2, 0 AS is_tbl_3 FROM table2
  UNION ALL
  SELECT id, city, 0 AS is_tbl_1, 0 AS is_tbl_2, 1 AS is_tbl_3 FROM table3
) AS combined_data
GROUP BY id, city
ORDER BY id, city;

逻辑说明：

每个子查询仅标记当前表的存在状态，其余表标记为0，UNION ALL全程不需要去重，执行效率高
最终按id和city分组，用MAX函数聚合即可得到对应哑变量的正确值，重复组合会自动合并为1

方案2：FULL OUTER JOIN 实现

如果你的数据库支持FULL OUTER JOIN，也可以用多表关联的方式实现：

SELECT 
  COALESCE(t1.id, t2.id, t3.id) AS id,
  COALESCE(t1.city, t2.city, t3.city) AS city,
  CASE WHEN t1.id IS NOT NULL THEN 1 ELSE 0 END AS is_tbl_1,
  CASE WHEN t2.id IS NOT NULL THEN 1 ELSE 0 END AS is_tbl_2,
  CASE WHEN t3.id IS NOT NULL THEN 1 ELSE 0 END AS is_tbl_3
FROM table1 t1
FULL OUTER JOIN table2 t2 
  ON t1.id = t2.id AND t1.city = t2.city
FULL OUTER JOIN table3 t3 
  ON COALESCE(t1.id, t2.id) = t3.id 
  AND COALESCE(t1.city, t2.city) = t3.city
ORDER BY id, city;

该方案适合表数量少的场景，表数量增加时JOIN条件复杂度会快速上升，性能低于方案1。