内连接表按日统计营收与支出总和的SQL语句求和错误排查

你的SQL语句求和结果错误的核心原因是一对多关联导致的重复行问题：trips表和expenses表是一对多的关系（一个行程对应多笔支出），直接用INNER JOIN关联后，每条行程记录会被重复生成多次（对应其下的每笔支出），这就导致sum(i.revenue)时，同一个行程的营收会被重复累加，最终结果自然偏离预期。

举个具体的例子：tripid=1有2笔支出，关联后这条行程的5000营收会被计算2次；tripid=2有3笔支出，3000营收会被计算3次，所以01/01/2020的总营收会错误地变成5000*2 + 3000*3 = 19000，但实际正确值应该是5000+3000=8000。

正确的SQL写法 #

我们需要先分别对营收和支出做聚合计算，再将结果关联，避免重复行的干扰。这里提供两种实用的写法：

方法1：用CTE先分别聚合再关联

-- 计算每日总营收
WITH daily_revenue AS (
    SELECT date, SUM(revenue) AS total_revenue
    FROM trips
    GROUP BY date
),
-- 计算每个行程的总支出，再按日期汇总
daily_expenses AS (
    SELECT t.date, SUM(e.amount) AS total_expenses
    FROM expenses e
    JOIN trips t ON e.tripid = t.id
    GROUP BY t.date
)
-- 关联两个聚合结果，用COALESCE处理无支出的日期
SELECT 
    dr.date,
    dr.total_revenue,
    COALESCE(de.total_expenses, 0) AS total_expenses
FROM daily_revenue dr
LEFT JOIN daily_expenses de ON dr.date = de.date
ORDER BY dr.date DESC;

方法2：在主查询中用子查询计算支出

SELECT
    t.date,
    SUM(t.revenue) AS total_revenue,
    SUM(COALESCE((SELECT SUM(e.amount) FROM expenses e WHERE e.tripid = t.id), 0)) AS total_expenses
FROM trips t
GROUP BY t.date
ORDER BY t.date DESC;

预期结果 #

用你的测试数据，执行正确SQL后会得到如下结果：

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