SQL中如何合并列并连接两个聚合查询结果以避免数据重复计数？

嗨，我来帮你搞定这个统计需求！你遇到的核心问题其实是直接关联原始的companies和team表会产生笛卡尔积——同一个国家下的公司和团队会两两配对，导致统计出来的数量被重复计算，出现虚增。正确的思路应该是先分别完成两个表的按国家统计，再把统计后的结果关联起来，这样就不会有重复数据啦。

步骤1：先单独完成两个表的统计 #

首先，我们先写出两个单独的统计查询，确保各自的结果符合预期：

统计各国家的公司数量 #

SELECT country, COUNT(*) AS company_no_supp
FROM companies
GROUP BY country;

这个查询会输出你想要的公司统计结果：

country | company_no_supp
--------+----------------
USA | 2
CAN | 1
MEX | 1

统计各国家的团队数量 #

SELECT country, COUNT(*) AS team_no_supp
FROM team
GROUP BY country;

这个查询会输出团队统计结果：

country | team_no_supp
--------+-----------
USA | 3
CAN | 3
MEX | 2

步骤2：关联两个统计结果得到最终数据集 #

现在我们把这两个统计后的结果用FULL JOIN关联（如果你的数据库不支持FULL JOIN，后面会给替代方案），关联条件是country，这样每个国家只会出现一次，不会产生重复：

通用方案（支持FULL JOIN的数据库：PostgreSQL、SQL Server等） #

SELECT 
    -- 处理可能存在的单边国家数据
    COALESCE(c.country, t.country) AS country,
    -- 如果某个国家没有团队，显示0而不是NULL
    COALESCE(t.team_no_supp, 0) AS team_no_supp,
    -- 如果某个国家没有公司，显示0而不是NULL
    COALESCE(c.company_no_supp, 0) AS company_no_supp
FROM (
    -- 子查询：统计公司数量
    SELECT country, COUNT(*) AS company_no_supp
    FROM companies
    GROUP BY country
) c
-- 全连接确保两边的国家都被包含
FULL JOIN (
    -- 子查询：统计团队数量
    SELECT country, COUNT(*) AS team_no_supp
    FROM team
    GROUP BY country
) t ON c.country = t.country
-- 按国家排序，结果更整洁
ORDER BY country;

MySQL等不支持FULL JOIN的替代方案 #

如果你的数据库不支持FULL JOIN，可以用UNION ALL把两个统计结果合并后再汇总：

SELECT 
    country,
    SUM(team_no_supp) AS team_no_supp,
    SUM(company_no_supp) AS company_no_supp
FROM (
    -- 公司统计，团队数设为0
    SELECT country, 0 AS team_no_supp, COUNT(*) AS company_no_supp
    FROM companies
    GROUP BY country
    UNION ALL
    -- 团队统计，公司数设为0
    SELECT country, COUNT(*) AS team_no_supp, 0 AS company_no_supp
    FROM team
    GROUP BY country
) combined
GROUP BY country
ORDER BY country;

结果验证 #

执行上面的查询后，你会得到完全符合预期的结果：

country | team_no_supp | company_no_supp
--------+--------------+-------------------
USA | 3 | 2
CAN | 3 | 1
MEX | 2 | 1

这个方法的核心就是先统计再关联，避免了原始表关联产生的笛卡尔积，从根源上解决了重复数据和数值虚增的问题。

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