如何合并含不同JOIN的多段SQL语句以获取统一分组统计结果？

我需要将三段可独立运行的SQL语句合并为一条，按company_name分组，得到包含company_name、employees（计数）、activities（计数）、projects（计数）的结果集。

原三段SQL语句 #

SELECT c.NAME AS "company_name",
    COUNT(c2.ID) AS "employees"
FROM contact c
    JOIN relation r ON c.ID = r.FID
    JOIN contact c2 ON r.CID = c2.ID
WHERE c.TYPE = 'ORG'
    AND c2.TYPE = 'PRS'
GROUP BY c.NAME;

SELECT c.NAME AS "company_name",
    COUNT(c3.ID) AS "activities"
FROM contact c
    JOIN relation r ON c.ID = r.FID
    JOIN activity c3 ON r.CID = c3.ID
WHERE c.TYPE = 'ORG'
GROUP BY c.NAME;

SELECT c.NAME AS "company_name",
    COUNT(c4.ID) AS "projects"
FROM contact c
    JOIN relation r ON c.ID = r.CID
    JOIN project c4 ON r.FID = c4.ID
WHERE c.TYPE = 'ORG'
GROUP BY c.NAME;

期望结果字段 #

• company_name
• employees (计数)
• activities (计数)
• projects (计数)

我尝试的错误语句 #

SELECT c.NAME AS "company_name",
    COUNT(
        CASE
            WHEN c2.TYPE = 'PRS' THEN c2.ID
        END
    ) AS "employees",
    COUNT(c3.ID) AS "activities",
    COUNT(c4.ID) AS "projects"
FROM contact c
    JOIN relation r ON c.ID = r.FID
    LEFT JOIN contact c2 ON r.CID = c2.ID
    AND c2.TYPE = 'PRS'
    LEFT JOIN activity c3 ON r.CID = c3.ID
    LEFT JOIN project c4 ON r.FID = c4.ID
WHERE c.TYPE = 'ORG'
GROUP BY c.NAME;

正确解决方案 #

方案一：子查询关联（推荐） #

该方案通过子查询单独计算每个维度的计数，避免多表连接产生的笛卡尔积导致计数失真，同时保证没有对应记录的公司显示0值：

SELECT 
    c.NAME AS "company_name",
    COALESCE(e.employees, 0) AS "employees",
    COALESCE(a.activities, 0) AS "activities",
    COALESCE(p.projects, 0) AS "projects"
FROM contact c
LEFT JOIN (
    -- 计算员工计数
    SELECT r.FID, COUNT(c2.ID) AS employees
    FROM relation r
    JOIN contact c2 ON r.CID = c2.ID
    WHERE c2.TYPE = 'PRS'
    GROUP BY r.FID
) e ON c.ID = e.FID
LEFT JOIN (
    -- 计算活动计数
    SELECT r.FID, COUNT(c3.ID) AS activities
    FROM relation r
    JOIN activity c3 ON r.CID = c3.ID
    GROUP BY r.FID
) a ON c.ID = a.FID
LEFT JOIN (
    -- 计算项目计数
    SELECT r.CID, COUNT(c4.ID) AS projects
    FROM relation r
    JOIN project c4 ON r.FID = c4.ID
    GROUP BY r.CID
) p ON c.ID = p.CID
WHERE c.TYPE = 'ORG'
ORDER BY c.NAME;

方案二：使用COUNT(DISTINCT)去重 #

如果不想用子查询，可以通过COUNT(DISTINCT)去除多表连接产生的重复记录，但要注意项目关联需要单独使用一个relation表别名（因为项目的关联条件与员工/活动不同）：

SELECT 
    c.NAME AS "company_name",
    COUNT(DISTINCT CASE WHEN c2.TYPE = 'PRS' THEN c2.ID END) AS "employees",
    COUNT(DISTINCT c3.ID) AS "activities",
    COUNT(DISTINCT c4.ID) AS "projects"
FROM contact c
LEFT JOIN relation r ON c.ID = r.FID
LEFT JOIN contact c2 ON r.CID = c2.ID AND c2.TYPE = 'PRS'
LEFT JOIN activity c3 ON r.CID = c3.ID
-- 单独关联项目对应的relation记录
LEFT JOIN relation r_project ON c.ID = r_project.CID
LEFT JOIN project c4 ON r_project.FID = c4.ID
WHERE c.TYPE = 'ORG'
GROUP BY c.NAME;

错误原因说明 #

你之前的语句存在两个问题：

1. 多表连接产生了笛卡尔积：员工、活动的记录会互相匹配，导致同一ID被多次统计，计数结果偏大。
2. 项目的关联条件错误：原项目查询中是c.ID = r.CID，但你复用了r表（关联条件是c.ID = r.FID），导致项目关联逻辑错误，无法正确统计。

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