SQLite同表Join与Group By查询：按日期统计账单收支及退货数据

表结构与需求 #

原始invoice表数据 #

统计需求 #

按bill_date分组，计算以下字段：

• discount：当日所有Normal Bill的discount总和（无数据则为0）
• expense：当日所有Normal Bill的expense总和（无数据则为0）
• returns：当日所有Return Bill的total总和（无数据则为0）
• total：计算公式为 Normal Bill的total总和 + expense总和 - discount总和 - returns

期望输出 #

原SQL的问题分析 #

你尝试的两个SQL存在以下问题：

1. 第一个SQL使用内连接（JOIN），会过滤掉只有Normal Bill或只有Return Bill的日期（比如2022-08-31没有Return Bill，会被内连接排除）。
2. 第二个SQL的子查询未分组，返回的是全量总和，后续分组无法得到按日期统计的结果，最终数据消失。

正确的SQL语句 #

推荐使用条件聚合的方式，无需多表连接，直接在单表中按日期分组并通过CASE WHEN筛选统计：

SELECT
    bill_date,
    COALESCE(SUM(CASE WHEN bill_type = 'Normal Bill' THEN discount END), 0) AS discount,
    COALESCE(SUM(CASE WHEN bill_type = 'Normal Bill' THEN expense END), 0) AS expense,
    COALESCE(SUM(CASE WHEN bill_type = 'Return Bill' THEN total END), 0) AS returns,
    -- 计算最终total
    COALESCE(SUM(CASE WHEN bill_type = 'Normal Bill' THEN total END), 0) +
    COALESCE(SUM(CASE WHEN bill_type = 'Normal Bill' THEN expense END), 0) -
    COALESCE(SUM(CASE WHEN bill_type = 'Normal Bill' THEN discount END), 0) -
    COALESCE(SUM(CASE WHEN bill_type = 'Return Bill' THEN total END), 0) AS total
FROM invoice
WHERE bill_date BETWEEN '2022-08-30' AND '2022-08-31'
GROUP BY bill_date
ORDER BY bill_date;

语句解释 #

• CASE WHEN：根据bill_type筛选对应的字段进行求和，非目标类型的记录返回NULL，不影响总和计算。
• COALESCE(..., 0)：将NULL转换为0，确保没有数据的日期对应字段显示为0（比如2022-08-30的discount和expense）。
• 最终total字段严格按照需求公式计算，确保结果准确。

验证结果 #

执行上述SQL后，会得到与期望完全一致的输出：

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