SQLite使用GROUP BY统计时补全缺失日期的实现方法

我来帮你搞定这个问题！你当前的SQL有几个关键问题导致没有销售记录的日期没显示出来，咱们一步步修正：

问题分析 #

1. JOIN方向错误：你用了Sale LEFT JOIN Dates，这会只保留有销售记录的日期，而我们需要保留所有生成的日期，应该用Dates LEFT JOIN Sale。
2. WHERE条件过滤了无销售行：原语句的WHERE Sale.time >= ...会把没有对应销售记录的日期行（此时Sale.time为NULL）过滤掉，这部分条件应该移到JOIN的ON子句中，只关联符合时间范围的销售记录。
3. COUNT统计逻辑不对：COUNT(*)会统计所有行（包括无销售的NULL行），所以永远不会返回0，应该统计Sale.id（或其他非NULL的字段），这样无销售时COUNT结果为0，再用COALESCE兜底更稳妥。
4. 排序逻辑错误：原语句按Sale.time DESC排序，无销售的行Sale.time为NULL会排到最后，应该按生成的日期Dates.date排序，保证日期顺序正确。

修正后的SQL语句 #

WITH RECURSIVE Dates(date) AS (
 VALUES(date('now', '-7 day'))
 UNION ALL
 SELECT date(date, '+1 day') FROM Dates WHERE date < date('now')
)
SELECT 
 strftime('%d/%m', Dates.date) AS Days, 
 COALESCE(COUNT(Sale.id), 0) AS Sales
FROM Dates 
LEFT JOIN Sale 
 ON strftime('%d/%m', DATETIME(Sale.time, 'unixepoch')) = strftime('%d/%m', Dates.date)
 AND Sale.time >= strftime('%s', 'now', '-7 day')
GROUP BY Dates.date 
ORDER BY Dates.date DESC;

代码说明 #

• Dates LEFT JOIN Sale：确保所有生成的日期都被保留，无论是否有销售记录。
• 将时间范围条件Sale.time >= ...移到ON子句：只关联指定时间内的销售记录，不会过滤掉无销售的日期行。
• COUNT(Sale.id)：只统计有销售记录的行，无销售时返回0，COALESCE确保即使COUNT返回NULL（实际不会，但更严谨）也显示0。
• GROUP BY Dates.date和ORDER BY Dates.date DESC：按生成的日期分组和排序，保证日期顺序正确。

目标输出示例 #

执行后你会得到如下格式的结果，无销售的日期会显示销售额0：

Days | Sales
-------------
27/12 | 10
26/12 | 2
25/12 | 0
24/12 | 2

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