MySQL仅用Join无子查询关联3张表并实现金额聚合的方法问询

可以仅用Join操作实现需求，但这类写法存在一定局限性（依赖数据无重复金额等前提），以下是具体实现：

一、按a_id分别统计B、C表的金额总和 #

假设数据库支持窗口函数，且同a_id下的金额记录无重复（即a_id+money可唯一标识单条记录），可使用以下SQL：

SELECT DISTINCT
    A.id AS a_id,
    -- 计算B表总和：修正笛卡尔积导致的重复计算
    COALESCE(
        SUM(B.money) OVER (PARTITION BY A.id) / COUNT(C.a_id) OVER (PARTITION BY A.id) * COUNT(DISTINCT CONCAT(C.a_id, C.money)) OVER (PARTITION BY A.id),
        0
    ) AS B_money_sum,
    -- 计算C表总和：修正笛卡尔积导致的重复计算
    COALESCE(
        SUM(C.money) OVER (PARTITION BY A.id) / COUNT(B.a_id) OVER (PARTITION BY A.id) * COUNT(DISTINCT CONCAT(B.a_id, B.money)) OVER (PARTITION BY A.id),
        0
    ) AS C_money_sum
FROM A
LEFT JOIN B ON A.id = B.a_id
LEFT JOIN C ON A.id = C.a_id

局限性：如果同a_id下存在金额完全相同的多条记录，COUNT(DISTINCT ...)会错误统计记录数，导致结果偏差。

二、按a_id统计两张表的金额总总和 #

基于同样的前提（无重复金额），可使用以下SQL：

SELECT DISTINCT
    A.id AS a_id,
    COALESCE(
        -- 累加修正后的B、C表总和
        (SUM(B.money) OVER (PARTITION BY A.id) / COUNT(C.a_id) OVER (PARTITION BY A.id) * COUNT(DISTINCT CONCAT(C.a_id, C.money)) OVER (PARTITION BY A.id)) +
        (SUM(C.money) OVER (PARTITION BY A.id) / COUNT(B.a_id) OVER (PARTITION BY A.id) * COUNT(DISTINCT CONCAT(B.a_id, B.money)) OVER (PARTITION BY A.id)),
        0
    ) AS money_sum
FROM A
LEFT JOIN B ON A.id = B.a_id
LEFT JOIN C ON A.id = C.a_id

局限性：与上述场景一致，存在重复金额时结果会出错。

补充：通用可靠的实现（含子查询） #

如果不限制子查询，更推荐先对B、C表分别聚合再关联，彻底避免笛卡尔积问题：

分别统计B、C总和 #

SELECT
    A.id AS a_id,
    COALESCE(B_sum.sum_money, 0) AS B_money_sum,
    COALESCE(C_sum.sum_money, 0) AS C_money_sum
FROM A
LEFT JOIN (SELECT a_id, SUM(money) sum_money FROM B GROUP BY a_id) B_sum ON A.id = B_sum.a_id
LEFT JOIN (SELECT a_id, SUM(money) sum_money FROM C GROUP BY a_id) C_sum ON A.id = C_sum.a_id

统计总总和 #

SELECT
    A.id AS a_id,
    COALESCE(SUM(combined.money), 0) AS money_sum
FROM A
LEFT JOIN (
    SELECT a_id, money FROM B
    UNION ALL
    SELECT a_id, money FROM C
) combined ON A.id = combined.a_id
GROUP BY A.id

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