BigQuery跨数据集多表处理：按ID聚合生成分表国家列

核心思路是分别对两张表按id分组聚合去重的国家列表，再通过id关联两个结果集，就能得到分属两列的目标数据。以下是主流数据库的实现示例：

MySQL/MariaDB #

SELECT
    COALESCE(t1.id, t2.id) AS id,
    t1.countries_from_table1,
    t2.countries_from_table2
FROM
    (SELECT
         id,
         GROUP_CONCAT(DISTINCT country ORDER BY country SEPARATOR ', ') AS countries_from_table1
     FROM table1
     GROUP BY id) t1
FULL OUTER JOIN
    (SELECT
         id,
         GROUP_CONCAT(DISTINCT country ORDER BY country SEPARATOR ', ') AS countries_from_table2
     FROM table2
     GROUP BY id) t2 ON t1.id = t2.id;

若你的MySQL版本不支持FULL OUTER JOIN，可改用UNION+LEFT JOIN替代：

SELECT
    combined.id,
    t1.countries_from_table1,
    t2.countries_from_table2
FROM
    (SELECT id FROM table1 UNION SELECT id FROM table2) combined
LEFT JOIN
    (SELECT
         id,
         GROUP_CONCAT(DISTINCT country ORDER BY country SEPARATOR ', ') AS countries_from_table1
     FROM table1
     GROUP BY id) t1 ON combined.id = t1.id
LEFT JOIN
    (SELECT
         id,
         GROUP_CONCAT(DISTINCT country ORDER BY country SEPARATOR ', ') AS countries_from_table2
     FROM table2
     GROUP BY id) t2 ON combined.id = t2.id;

PostgreSQL #

SELECT
    COALESCE(t1.id, t2.id) AS id,
    t1.countries_from_table1,
    t2.countries_from_table2
FROM
    (SELECT
         id,
         STRING_AGG(DISTINCT country, ', ' ORDER BY country) AS countries_from_table1
     FROM table1
     GROUP BY id) t1
FULL OUTER JOIN
    (SELECT
         id,
         STRING_AGG(DISTINCT country, ', ' ORDER BY country) AS countries_from_table2
     FROM table2
     GROUP BY id) t2 ON t1.id = t2.id;

SQL Server #

SELECT
    COALESCE(t1.id, t2.id) AS id,
    t1.countries_from_table1,
    t2.countries_from_table2
FROM
    (SELECT
         id,
         STRING_AGG(DISTINCT country, ', ') WITHIN GROUP (ORDER BY country) AS countries_from_table1
     FROM table1
     GROUP BY id) t1
FULL OUTER JOIN
    (SELECT
         id,
         STRING_AGG(DISTINCT country, ', ') WITHIN GROUP (ORDER BY country) AS countries_from_table2
     FROM table2
     GROUP BY id) t2 ON t1.id = t2.id;

关键说明 #

• DISTINCT确保每个国家在列表中仅出现一次
• ORDER BY用于规整列表顺序（可选）
• COALESCE处理仅在单张表中存在的id，避免id列出现NULL
• 若只需要同时存在于两张表的id，将FULL OUTER JOIN替换为INNER JOIN即可

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