SQL查询返回双倍总金额问题求助

核心原因 #

查询返回错误总计的本质是多表连接产生笛卡尔积：

• 针对project_files.id=2的记录，funding_lines有2条数据（14500、500），works有2条数据
• 直接关联后会生成2×2=4条中间结果，每条funding_lines的金额被重复计算2次，最终总和被放大为预期值的2倍。

修复方案 #

正确的思路是先分别对关联表按project_file_id聚合，再与主表关联，彻底避免重复计算：

SELECT 
    pf.id, 
    pf.identifier_code,
    fl.total_estimated,
    fl.total_notified,
    w.works_list,
    w.work_types
FROM project_files pf
-- 先聚合资金线的金额总和
JOIN (
    SELECT 
        project_file_id,
        SUM(estimated_amount) AS total_estimated,
        ROUND(SUM(notified_amount), 2) AS total_notified
    FROM funding_lines
    GROUP BY project_file_id
) fl ON pf.id = fl.project_file_id
-- 先聚合工作内容与类型
JOIN (
    SELECT 
        project_file_id,
        GROUP_CONCAT(DISTINCT description SEPARATOR ', ') AS works_list,
        GROUP_CONCAT(DISTINCT wtc.name SEPARATOR ', ') AS work_types
    FROM works w
    JOIN work_type_config wtc ON w.type_id = wtc.id
    GROUP BY project_file_id
) w ON pf.id = w.project_file_id
WHERE pf.id = 2;

方案说明 #

1. 子查询fl先完成funding_lines的金额求和，确保每个项目只有一条汇总记录
2. 子查询w先完成works的描述拼接与类型汇总，同样每个项目只有一条记录
3. 最后将两个聚合后的子查询与project_files关联，完全避免笛卡尔积导致的重复计算

执行该查询后，total_notified会正确返回15000.00。

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