数据表a记录所有数据,首先按天统计数据量
id | date | num |
1 | 2021-12-01 | 1 |
2 | 2021-12-01 | 1 |
3 | 2021-12-02 | 1 |
4 | 2021-12-06 | 1 |
select date,count(*)total from a group by date
得到结果
2021-12-01 | 2 |
2021-12-02 | 1 |
2021-12-06 | 1 |
然后需要补齐没有数据的日期,希望得到的结果为
... | ... |
2021-12-01 | 2 |
2021-12-02 | 1 |
2021-12-03 | 0 |
2021-12-04 | 0 |
2021-12-05 | 0 |
2021-12-06 | 1 |
... | ... |
一般需要使用个日历表来关联得出所有日期的结果,也可用
SELECT
CONVERT (VARCHAR (100),dateadd(d, number, GETDATE() - 364),23) AS day
FROM
master..spt_values n
WHERE
n.type = 'p'
AND n.number <= 364