使用GROUP BY时SQL求和结果翻倍的查询问题

你的查询结果不符合预期，核心原因是直接JOIN time和bills表时，项目的每条工时记录会与该项目的所有账单记录匹配，导致工时被重复统计。比如项目1有1条16小时的工时记录，对应2条账单，JOIN后会生成2条相同的16小时记录，SUM时就变成了16×2=32。

下面分两种场景给出解决方案：

场景1：仅获取有已支付账单的项目总工时 #

如果只需要筛选出标记为已支付（marked='P'）的项目，并展示它们的总工时，用EXISTS子查询替代JOIN可以避免重复行：

SELECT 
    project_id,
    SUM(hours) AS total_hours
FROM time
WHERE EXISTS (
    SELECT 1 
    FROM bills 
    WHERE bills.project_id = time.project_id 
      AND marked = 'P'
)
GROUP BY project_id;

场景2：同时展示总工时和已支付工时数 #

如果需要同时输出来自time表的项目总工时和来自bills表的已支付工时，建议分别对两个表做聚合后再关联，确保数据不会重复计算：

SELECT 
    COALESCE(t.project_id, b.project_id) AS project_id,
    COALESCE(t.total_hours, 0) AS total_hours,
    COALESCE(b.paid_hours, 0) AS paid_hours
FROM (
    -- 统计每个项目的总工时
    SELECT project_id, SUM(hours) AS total_hours
    FROM time
    GROUP BY project_id
) t
-- 用FULL JOIN确保不会遗漏只有工时或只有账单的项目
FULL JOIN (
    -- 统计每个项目的已支付工时
    SELECT project_id, SUM(hours) AS paid_hours
    FROM bills
    WHERE marked = 'P'
    GROUP BY project_id
) b ON t.project_id = b.project_id;

这个查询会给项目1返回project_id=1, total_hours=16, paid_hours=16的正确结果，同时兼容没有工时记录或没有账单记录的项目。

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