如何将两张表的SQL查询结果按列合并为单表或视图？

要实现将两个查询的结果按列合并（同一lingkungan_id对应两组统计列），核心是将两个查询作为子查询，通过lingkungan_id字段关联，同时为两组统计列添加区分别名，具体方案如下：

通用解决方案（支持FULL JOIN的数据库） #

SELECT 
    COALESCE(t1.lingkungan_id, t2.lingkungan_id) AS lingkungan_id,
    t1.L AS L_sidi,
    t1.P AS P_sidi,
    t1.JLH AS JLH_sidi,
    t2.L AS L_pmk,
    t2.P AS P_pmk,
    t2.JLH AS JLH_pmk
FROM 
    (
        -- 第一个查询作为子查询t1
        SELECT jk.lingkungan_id,
               SUM(CASE WHEN j.sidi_id=1 AND j.jk=1 THEN 1 ELSE 0 END) L,
               SUM(CASE WHEN j.sidi_id=1 AND j.jk=2 THEN 1 ELSE 0 END) P,
               SUM(CASE WHEN j.sidi_id=1 THEN 1 ELSE 0 END) JLH
        FROM `jemaat` j
        JOIN `jemaat_kel` jk ON jk.nokk = j.nokk
        GROUP BY jk.lingkungan_id
    ) t1
FULL JOIN 
    (
        -- 第二个查询作为子查询t2
        SELECT pmk.lingkungan_id,
               SUM(CASE WHEN pmk.jk=1 THEN 1 ELSE 0 END) L,
               SUM(CASE WHEN pmk.jk=2 THEN 1 ELSE 0 END) P,
               SUM(CASE WHEN pmk.jk IN(1,2) THEN 1 ELSE 0 END) JLH
        FROM `tbl_pmk` pmk
        JOIN `jemaat` j ON pmk.jemaat_id = j.jemaat_id
        GROUP BY pmk.lingkungan_id
    ) t2 ON t1.lingkungan_id = t2.lingkungan_id
ORDER BY COALESCE(t1.lingkungan_id, t2.lingkungan_id);

关键说明 #

• FULL JOIN：确保所有lingkungan_id都被包含，不管它只存在于第一个还是第二个查询结果中
• COALESCE：处理其中一个子查询中lingkungan_id为NULL的情况，保证该列始终有值
• 列别名：为两组统计列添加不同前缀（如L_sidi、L_pmk），避免列名冲突同时清晰区分数据来源

MySQL兼容方案（MySQL不支持FULL JOIN） #

如果使用MySQL，可先通过UNION获取所有唯一的lingkungan_id，再分别左连接两个子查询：

SELECT 
    base.lingkungan_id,
    t1.L AS L_sidi,
    t1.P AS P_sidi,
    t1.JLH AS JLH_sidi,
    t2.L AS L_pmk,
    t2.P AS P_pmk,
    t2.JLH AS JLH_pmk
FROM 
    (
        SELECT lingkungan_id FROM (
            SELECT jk.lingkungan_id FROM `jemaat` j JOIN `jemaat_kel` jk ON jk.nokk = j.nokk
            UNION
            SELECT pmk.lingkungan_id FROM `tbl_pmk` pmk JOIN `jemaat` j ON pmk.jemaat_id = j.jemaat_id
        ) AS combined_ids
    ) base
LEFT JOIN 
    (
        SELECT jk.lingkungan_id,
               SUM(CASE WHEN j.sidi_id=1 AND j.jk=1 THEN 1 ELSE 0 END) L,
               SUM(CASE WHEN j.sidi_id=1 AND j.jk=2 THEN 1 ELSE 0 END) P,
               SUM(CASE WHEN j.sidi_id=1 THEN 1 ELSE 0 END) JLH
        FROM `jemaat` j
        JOIN `jemaat_kel` jk ON jk.nokk = j.nokk
        GROUP BY jk.lingkungan_id
    ) t1 ON base.lingkungan_id = t1.lingkungan_id
LEFT JOIN 
    (
        SELECT pmk.lingkungan_id,
               SUM(CASE WHEN pmk.jk=1 THEN 1 ELSE 0 END) L,
               SUM(CASE WHEN pmk.jk=2 THEN 1 ELSE 0 END) P,
               SUM(CASE WHEN pmk.jk IN(1,2) THEN 1 ELSE 0 END) JLH
        FROM `tbl_pmk` pmk
        JOIN `jemaat` j ON pmk.jemaat_id = j.jemaat_id
        GROUP BY pmk.lingkungan_id
    ) t2 ON base.lingkungan_id = t2.lingkungan_id
ORDER BY base.lingkungan_id;

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