如何通过Left Join与预聚合实现多表求和统计

问题原因 #

直接同时Left Join app_pay和app_receive到app_company会产生笛卡尔积：当某公司在两个表中都有多条记录时，每条支付记录会和每条收款记录配对，导致求和时数值被重复计算。比如Microsoft有2条支付记录、1条收款记录，连接后会生成2行相同的收款数据，求和时就变成70000*2=140000。

解决方案：先聚合再关联 #

先分别对支付、收款数据按公司+时间范围预聚合，得到每个公司的总支付、总收款，再和公司表关联，避免笛卡尔积。

正确SQL语句 #

SELECT 
    o.company,
    COALESCE(p.total_pay, 0) AS value_pay,
    COALESCE(r.total_receive, 0) AS value_receive
FROM app_company AS o
LEFT JOIN (
    -- 预聚合支付数据：按公司统计指定时间段总支付
    SELECT 
        company,
        SUM(valuep) AS total_pay
    FROM app_pay
    WHERE date(duedate) BETWEEN '2022-10-01' AND '2022-10-30'
    GROUP BY company
) AS p ON o.idcompany = p.company
LEFT JOIN (
    -- 预聚合收款数据：按公司统计指定时间段总收款
    SELECT 
        company,
        SUM(valuer) AS total_receive
    FROM app_receive
    WHERE date(issuedate) BETWEEN '2022-10-01' AND '2022-10-30'
    GROUP BY company
) AS r ON o.idcompany = r.company
ORDER BY o.company;

关键说明 #

1. 预聚合子查询：分别对app_pay和app_receive做分组求和，确保每个公司只有一行汇总数据，从根源消除笛卡尔积隐患。
2. COALESCE函数：将NULL值替换为0，让报表数值展示更统一（不需要的话可直接去掉，保留NULL）。
3. 时间过滤前置：在预聚合阶段就过滤时间范围，减少后续关联的数据量，提升查询效率。

正确结果预览 #

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