如何在SQL中合并多组聚合查询生成含多计算字段的结果表

不要直接将两个业务表同时关联grouped_users表做聚合——如果同一用户在两个业务表中都存在多条记录，直接关联会产生笛卡尔积，导致SUM计算结果虚高，推荐先分别完成两个维度的聚合计算，再按分组ID关联结果，写法兼容性最好、计算逻辑最准确。

推荐写法（支持CTE的数据库：MySQL8.0+、PostgreSQL、SQL Server等） #

用CTE提前封装两个单计算的聚合逻辑，再按group_id关联，逻辑清晰易维护：

WITH res_calc1 AS (
    SELECT
        SUM(friends_made) AS calc1,
        table2.group_id
    FROM friends_made_table AS table1
    INNER JOIN grouped_users AS table2
        ON table1.user_id = table2.user_id
    GROUP BY table2.group_id
),
res_calc2 AS (
    SELECT
        SUM(request_accept) AS calc2,
        table1.group_id
    FROM friends_accept_table AS table3
    INNER JOIN grouped_users AS table1
        ON table1.user_id = table3.user_id
    GROUP BY table1.group_id
)
SELECT
    rc1.calc1,
    rc2.calc2,
    rc1.group_id
FROM res_calc1 rc1
INNER JOIN res_calc2 rc2
    ON rc1.group_id = rc2.group_id;

兼容低版本数据库写法（不支持CTE的场景） #

直接把两个聚合逻辑作为子查询写在FROM子句中，效果和CTE写法完全一致：

SELECT
    c1.calc1,
    c2.calc2,
    c1.group_id
FROM (
    SELECT
        SUM(friends_made) AS calc1,
        table2.group_id
    FROM friends_made_table AS table1
    INNER JOIN grouped_users AS table2
        ON table1.user_id = table2.user_id
    GROUP BY table2.group_id
) c1
INNER JOIN (
    SELECT
        SUM(request_accept) AS calc2,
        table1.group_id
    FROM friends_accept_table AS table3
    INNER JOIN grouped_users AS table1
        ON table1.user_id = table3.user_id
    GROUP BY table1.group_id
) c2
ON c1.group_id = c2.group_id;

补充说明 #

示例中两个查询返回的group_id完全匹配，用INNER JOIN关联即可得到预期的目标结果。如果后续业务中存在某个分组ID只出现在其中一个计算结果里的场景，可以根据需求替换为LEFT JOIN/FULL JOIN保留对应侧的分组数据。

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