MySQL SUM函数结果异常翻倍及项目盈亏查询优化求助

问题根源分析 #

1. 无工时表的项目不显示：原查询用了JOIN expenses（内连接），只有存在对应开支记录的项目才会被纳入结果，导致没有开支的项目直接被过滤。
2. 计算结果翻倍：expenses和timesheets都是projects的一对多关联表，直接同时关联会产生笛卡尔积（比如一个项目有2条开支、3条工时记录，关联后会生成6条记录），求和时会重复计算数值。

正确的单条SQL查询 #

通过子查询预先计算每个项目的总开支和总人工成本，再与projects表关联，避免笛卡尔积问题，同时保证所有项目都能被查询到：

SELECT
    p.id AS Pro_ID,
    p.customer AS Pro_Customer,
    p.job_reference AS Pro_Ref,
    p.value AS Pro_Value,
    p.end_date AS Pro_End,
    COALESCE(e.total_expense, 0) AS expense,
    COALESCE(t.total_labour, 0) AS LabourCost,
    ROUND(p.value - COALESCE(e.total_expense, 0) - COALESCE(t.total_labour, 0), 2) AS Profit
FROM
    projects p
LEFT JOIN (
    -- 子查询计算每个项目的总开支
    SELECT project_id, SUM(exp_amount) AS total_expense
    FROM expenses
    GROUP BY project_id
) e ON e.project_id = p.id
LEFT JOIN (
    -- 子查询计算每个项目的总人工成本
    SELECT project_id, SUM(pay_total) AS total_labour
    FROM timesheets
    WHERE project_ref = (SELECT job_reference FROM projects WHERE id = project_id)
    GROUP BY project_id
) t ON t.project_id = p.id
ORDER BY p.end_date ASC;

关键调整说明 #

• 用LEFT JOIN替代原查询的内连接，确保所有项目（无论有无开支、工时记录）都能出现在结果中。
• 两个子查询分别对expenses和timesheets按项目分组求和，避免了多表直接关联产生的笛卡尔积，保证计算数值准确。
• 使用COALESCE函数将NULL值转为0，确保没有开支或工时的项目对应数值显示为0，而非NULL。
• 利润计算修正为项目价值 - 总开支 - 总人工成本，符合实际盈亏逻辑。

疑问解答 #

• 单条查询完全可以实现需求，不需要PHP/JS后续处理。
• 必须使用子查询（或派生表）来预先聚合一对多表的数据，否则无法避免笛卡尔积导致的计算错误。

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