按日、月统计数据补全

1、首先需要一个完整的日期列表

SQLSERVER

SELECT DATEADD(MONTH, number, '2018-11-01') dt FROM master..spt_values WHERE type = 'p' AND dateadd(MONTH, number, '2018-11-01') <= '2019-11-30';

SELECT DATEADD(DAY, number, '2018-11-01') dt FROM master..spt_values WHERE type = 'p' AND dateadd(DAY, number, '2018-11-01') <= '2019-11-30';

MYSQL

SELECT @s :=@s + 1 AS _index, DATE( DATE_SUB('2020-06-10', INTERVAL @s DAY) ) AS _date FROM information_schema. TABLES, (SELECT @s := - 1) temp WHERE @s < 30 ORDER BY _date

SELECT @s :=@s + 1 AS _index, DATE_FORMAT( DATE( DATE_SUB('2020-06-10', INTERVAL @s MONTH) ) ,'%Y-%m')AS _date FROM information_schema. TABLES, (SELECT @s := - 1) temp WHERE @s < 10 ORDER BY _date

2、关联数据进行具体查询（一边使用left join）