内连接表中日度收入与支出求和结果错误排查

嘿，这个问题我太熟悉了——你踩了一对多关联后直接聚合的经典坑！让我给你拆解清楚：

为什么你的SQL结果错误？ #

当你直接把trips和expenses做内连接时，因为一个trip对应多条expense记录，trips里的每条revenue会被重复输出多次（次数等于对应expense的数量）。举个例子：

• trip id=1有2条expense，连接后它的5000元收入会出现2次
• trip id=2有3条expense，它的3000元收入会出现3次

这时候用sum(i.revenue)计算的话，01/01的总收入会变成5000*2 + 3000*3 = 19000，而实际应该是5000+3000=8000，完全虚高！

另外你的SQL还有两个小问题：

1. 用INNER JOIN会过滤掉没有支出的日期（比如02/01/2020），不符合"统计每日"的需求
2. GROUP BY i.date但SELECT里包含i.id，这在严格SQL模式下会报错，而且返回的id是随机的，没有意义

正确的解决思路 #

先分别统计每日总收入和每日总支出，再把两个统计结果关联起来，避免数据重复计算：

方案一：使用CTE（清晰易读） #

WITH daily_total_revenue AS (
    -- 先按日期统计总收入
    SELECT date, SUM(revenue) AS total_revenue
    FROM trips
    GROUP BY date
),
trip_total_expenses AS (
    -- 先按trip统计单条trip的总支出
    SELECT tripid, SUM(amount) AS trip_expense
    FROM expenses
    GROUP BY tripid
),
daily_total_expenses AS (
    -- 再关联trips表，按日期统计总支出
    SELECT t.date, SUM(te.trip_expense) AS total_expense
    FROM trips t
    LEFT JOIN trip_total_expenses te ON t.id = te.tripid
    GROUP BY t.date
)
-- 最后关联每日收入和支出，用COALESCE处理无支出的日期
SELECT 
    dr.date,
    dr.total_revenue,
    COALESCE(de.total_expense, 0) AS total_expense
FROM daily_total_revenue dr
LEFT JOIN daily_total_expenses de ON dr.date = de.date
ORDER BY dr.date DESC;

方案二：更简洁的子查询写法 #

SELECT 
    t.date,
    -- 子查询直接按日期计算总收入，避免重复
    (SELECT SUM(revenue) FROM trips WHERE date = t.date) AS total_revenue,
    -- 按日期汇总所有关联的支出
    COALESCE(SUM(e.amount), 0) AS total_expense
FROM trips t
LEFT JOIN expenses e ON t.id = e.tripid
GROUP BY t.date
ORDER BY t.date DESC;

验证结果 #

执行正确的SQL后，你会得到符合预期的结果：

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