SQLite按所选日期范围内的所有日期分组，即使数据不存在

Question

我有一个表invoice，它有两列，date和total我想按一个月的所有日子获得最后30天总组(或者按一周的所有日子获取最后7天组(

date     total
11/16    500
11/23    200
12/2     400
12/4     600

我试过这样的东西，

SELECT strftime('%m/%d', Date) as valDay, SUM(Total) 
FROM invoice  
GROUP BY valDay 
Order By Date DESC LIMIT 30 ;

它给出了最后30条记录。但是我想要过去30天的记录，即使表中有几天没有数据(它必须返回0作为那几天的总数(

Answer 1

您可以使用递归查询来生成日期，然后使用左联接来创建表，并聚合：

with recursive cte as (
select date('now') as dt,  date('now', '-30 day') last_dt
union all select date(dt, '-1 day'), last_dt from cte where dt > last_dt
)
select c.dt, coalesce(sum(i.total), 0) as total
from cte c
left join invoice i on i.date >= c.dt and i.date < date(c.dt, '+1 day')
group by c.dt
order by c.dt desc