mysql按日、时统计，空值填充0

需求

mysql按日、时统计时，单纯用group by会出现空值，故需要新建一个时间序列，再通过左连的形式来统计
如下

实现

1、利用mysql.help_topic表

select @a:=@a + 1 AS `index`, DATE_FORMAT(ADDDATE( "2023-03-01 15:23:18" ,INTERVAL @a DAY),'%Y-%m-%d') AS date FROM mysql.help_topic,(SELECT @a:=0) temp LIMIT 30

这是一条MySQL的SQL语句，它用来生成一个日期序列，包括从"2023-03-01"开始的连续的30天日期。下面是语句的详细解释：
首先，定义一个变量@a并将其初始化为0，这样就可以在后续的生成日期序列中使用。
然后，将mysql.help_topic表与子查询(SELECT @a:=0) temp连接起来，连接条件是没有连接条件，（这里目的是让子查询只执行一次，并且返回一行数据，初始化@a变量）。
接下来就是生成日期序列的逻辑：使用DATE_FORMAT函数将以@a为天数增量的日期计算出来，并将其格式化为'%Y-%m-%d'的日期字符串表示。这里ADDDATE函数将"2023-03-01 15:23:18"增加了@a天，然后通过DATE_FORMAT函数将其格式化为"%Y-%m-%d"的日期字符串表示。
最后，为了控制日期序列的长度，使用LIMIT语句限制了返回结果的数量，这里是30个日期。
最终，通过这条SQL语句，可以生成一个长度为30的日期序列，并将其作为结果集返回，其中列名为index，表示日期序列中日期的顺序，另一列为date，表示按格式化后的日期字符串表示的日期值。

-- 结果
1	2023-03-02
2	2023-03-03
3	2023-03-04
4	2023-03-05
5	2023-03-06
6	2023-03-07
7	2023-03-08
...

-- 设备历史数量查询-日
SELECT date_format(c.date, '%c月%e日') AS date, IFNULL(c.count,0) AS count 
FROM ( 
SELECT * FROM 
( select @a:=@a + 1 AS `index`, DATE_FORMAT(ADDDATE( "2023-03-01 15:23:18" ,INTERVAL @a DAY),'%Y-%m-%d') AS date FROM mysql.help_topic,(SELECT @a:=0) temp LIMIT 30 ) 
AS t1 LEFT JOIN (
SELECT date_format(td.created, '%Y-%m-%d') AS dateDay, count(*) as count FROM t_device td WHERE td.created > "2023-03-01 15:23:18" AND td.created < "2023-03-31 15:23:18" and td.is_delete=0 group by date_format(td.created, '%Y-%m-%d') )
AS t2 ON t1.date = t2.dateDay) AS c ORDER BY date_format(c.date, '%Y-%m-%d') asc

-- 结果
3月2日	0
3月3日	0
3月4日	0
3月5日	0
3月6日	0
3月7日	0
3月8日	0
3月9日	0
3月10日	0
3月11日	0
3月12日	0
3月13日	0
3月14日	0
3月15日	0
3月16日	0
3月17日	0
3月18日	0
3月19日	0
3月20日	0
3月21日	0
3月22日	0
3月23日	0
3月24日	0
3月25日	0
3月26日	0
3月27日	0
3月28日	0
3月29日	1
3月30日	7
3月31日	2

2、利用union

SELECT @xi:=@xi+1 as `index` from
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) xc1,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) xc2,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) xc3,
(SELECT @xi:=0) xc0

这是一条MySQL的SQL语句，目的是生成包含1~125的自然数序列。下面是该语句的详细解释：
首先，定义变量@xi并将其初始化为0，这样就可以在后续的生成数字序列中使用。
然后，将五个SELECT语句的结果集连接在一起，从而得到一个包含125个记录的虚拟表。这里使用了笛卡尔积的方法，将多个SELECT语句的结果集进行连接。
最后，通过SELECT子句在每个记录上执行算术运算（@xi:=@xi+1），将@xi变量的值递增1，并将其作为一个新的列添加到结果集中，以此生成包含1~125的自然数序列。
最终，通过这条SQL语句，可以生成一个包含1~125的自然数序列，并将其作为结果集返回，其中列名为index，表示数字序列中数字的顺序。

其中
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) xc2 需要多少个就写多少个，日期统计比较少的话，比较适合
-- 结果 
1
2
3
4
5
6
7
...

最终sql

SELECT date_format(c.date, '%c月%e日') AS date, IFNULL(c.count,0) AS count 
FROM ( 
SELECT * FROM 
(
SELECT `index`,DATE_FORMAT(ADDDATE( "2023-03-22 15:23:18" ,INTERVAL `index` DAY),'%Y-%m-%d') as date
FROM ( 
 SELECT @xi:=@xi+1 as `index` from 
 (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) xc1, 
 (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) xc2, 
 (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) xc3,   
 (SELECT @xi:=0) xc0 
) xcxc LIMIT 30 
) 
AS t1 LEFT JOIN (
SELECT date_format(td.active_time, '%Y-%m-%d') AS dateDay, count(*) as count FROM t_device td WHERE td.active_time > "2023-03-22 15:23:18" AND td.active_time < "2023-04-21 15:23:18" and td.is_delete=0 and td.on_line!=0 group by date_format(td.active_time, '%Y-%m-%d') )
AS t2 ON t1.date = t2.dateDay) AS c ORDER BY date_format(c.date, '%Y-%m-%d') asc


-- 结果
3月23日	0
3月24日	0
3月25日	0
3月26日	0
3月27日	0
3月28日	10
3月29日	0
3月30日	0
3月31日	4
4月1日	0
4月2日	0
4月3日	0
4月4日	0
4月5日	0
4月6日	5
4月7日	0
4月8日	0
4月9日	0
4月10日	0
4月11日	0
4月12日	0
4月13日	1
4月14日	0
4月15日	0
4月16日	0
4月17日	0
4月18日	0
4月19日	0
4月20日	0
4月21日	0