sql - 计算表计数时如何添加缺少的日期

Question

我有一个销售表，记录了一个项目的销售情况。简化版如下


ID
商品编号
销售时间


1
1234
2020-12-01 12:44:22

2
1234
2020-12-01 17:12:22

3
1234
2020-12-02 12:44:22

4
1234
2020-12-04 17:12:22


我正在写一个查询来计算每天售出的物品，它工作正常并给出以下结果。


ID
商品编号
日期
销售数量


1
1234
2020-12-01
2

2
1234
2020-12-02
1

3
1234
2020-12-04
1


我如何包括没有销售的天数为零，如下所示。


ID
商品编号
日期
销售数量


1
1234
2020-12-01
2

2
1234
2020-12-02
1

3
1234
2020-12-03
0

4
1234
2020-12-04
1

Answer 1

一种选择使用递归查询来生成日期。然后你可以 cross join使用表格中可用的不同项目列表，并为表格带来 left join .最后一步是聚合:

with cte as (
    select min(convert(date, saletime)) as dt, max(convert(date, saletime)) as max_dt from mytable
    union all
    select dateadd(day, 1, dt), max_dt from cte where dt < max_dt
)
select c.dt, i.itemid, count(t.id) as sale_count
from cte c
cross join (select distinct itemid from mytable) i
left join mytable t 
    on  t.itemid = i.itemid
    and t.date >= c.dt
    and t.date <  dateadd(day, 1, c.dt)
group by c.dt, i.itemid

在现实生活中，您可能有一个单独的引用表来存储项目，您将使用它来代替 select distinct。子查询。