如何将PKS、IA、MOU三张表按Satker字段合并为目标汇总表？

问题原因 #

你原有写法直接对三张未聚合的表执行LEFT JOIN，当同一个Satker在多个表存在多条记录时，会产生笛卡尔积，关联后生成大量重复行，count统计的是关联后的行数而非各表原始匹配条数，因此结果不符合预期。

推荐解决方案 #

优先采用先单表聚合再关联的写法，逻辑清晰且不会出现统计偏差：

-- 先分别统计每个表各Satker的数量
WITH pks_agg AS (
    SELECT satuan_kerja AS Satker, COUNT(*) AS PKS
    FROM pks
    GROUP BY satuan_kerja
),
ia_agg AS (
    SELECT satuan_kerja AS Satker, COUNT(*) AS IA
    FROM pelaksanaan_kerjasama
    GROUP BY satuan_kerja
),
mou_agg AS (
    SELECT subsatker_mou AS Satker, COUNT(*) AS MOU
    FROM mou
    GROUP BY subsatker_mou
),
-- 收集三张表所有出现过的Satker，避免遗漏
all_satker AS (
    SELECT Satker FROM pks_agg
    UNION
    SELECT Satker FROM ia_agg
    UNION
    SELECT Satker FROM mou_agg
)
-- 关联获取最终结果
SELECT 
    s.Satker,
    p.PKS,
    i.IA,
    m.MOU
FROM all_satker s
LEFT JOIN pks_agg p ON s.Satker = p.Satker
LEFT JOIN ia_agg i ON s.Satker = i.Satker
LEFT JOIN mou_agg m ON s.Satker = m.Satker;

如果需要无匹配值时填0而非留空，把对应字段替换为COALESCE(p.PKS, 0)、COALESCE(i.IA, 0)、COALESCE(m.MOU, 0)即可。

备选简化方案 #

如果你的数据库支持UNION ALL操作，也可以用行转列的条件聚合写法：

SELECT 
    Satker,
    SUM(CASE WHEN source = 'PKS' THEN 1 END) AS PKS,
    SUM(CASE WHEN source = 'IA' THEN 1 END) AS IA,
    SUM(CASE WHEN source = 'MOU' THEN 1 END) AS MOU
FROM (
    SELECT satuan_kerja AS Satker, 'PKS' AS source FROM pks
    UNION ALL
    SELECT satuan_kerja AS Satker, 'IA' AS source FROM pelaksanaan_kerjasama
    UNION ALL
    SELECT subsatker_mou AS Satker, 'MOU' AS source FROM mou
) t
GROUP BY Satker;

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