SQL-按每天日期查询数据 没有数据缺失数据返回0 无需建表 若无当日数据也返回值 图表按天查询数据返回

本博客环境背景：JAVA项目 & MYSQL数据库 ，需要能够按每日日期返回当天的数据值，若当日无数据应返回0。否则会造成当日数据缺失，值对应不上等问题。

看了很多实现方案都是直接在数据库里再建立一张时间表，存放日期数据，然后再联合查询。当时觉得再建一张表然后去改之前的所有内容有点太繁琐了，，然后发现可以创建时间虚拟表，直接改SQL联合查询即可，但其实最后发现也还是挺繁琐的。。大家选其一实现即可。

1、SQL创建虚拟日期表

直接使用如下语句可以创建一张虚拟日期表，可以按照从你需要的业务表（table1）中从取

SELECT
  @cdate: = date_add(@cdate, interval - 1 day) as date_str
FROM(SELECT @cdate: = date_add(CURDATE(), interval + 1 day) from table1) t1
1
2
3

2、业务逻辑查询需要和日期表查询的日期别名一致

create_date是逻辑按创建日期来查询，使用DATE_FORMAT()可以将“yyyy-MM-dd HH:mm:ss” 格式时间转成所需的“yyyy-MM-dd”日期格式。
日期只是示例查询样式，实际可以换成所需的startTime与endTime。

SELECT DATE_FORMAT(a.create_date, '%Y-%m-%d') as date_str, count(*) as date_count FROM table1 as a WHERE a.create_date>="2022-05-10 00:00:00" and a.create_date<"2022-09-13 23:59:00" 
    GROUP BY DATE_FORMAT(a.create_date, '%Y-%m-%d')
1
2

3、两个查询用左连接 联合查询

需要使用左连接将逻辑查询与虚拟日期表联合起来查询。date_str是日期字段，date_count是所需要的统计数据字段
日期只是示例查询样式，实际可以换成所需的startTime与endTime；同时需要限制返回日期范围在最外面加上了同样的查询时间限制，实际如有需要最外层where这块也应要用DATE_FORMAT()适配转换一下
最后ORDER BY date_str , ASC默认升序，DESC默认降序

SELECT t1.date_str, COALESCE(t2.date_count, 0) as date_count
FROM(
    SELECT @cdate:=date_add(@cdate, interval - 1 day) as date_str FROM(SELECT @cdate:=date_add(CURDATE(), interval + 1 day) from task) tmp1
) t1
LEFT JOIN(
    SELECT DATE_FORMAT(a.create_date, '%Y-%m-%d') as date_str, count(*) as date_count FROM table1 as a WHERE a.create_date>="2022-05-10 00:00:00" and a.create_date<"2022-09-13 23:59:00" 
    GROUP BY DATE_FORMAT(a.create_date, '%Y-%m-%d') 
) t2
on t1.date_str = t2.date_str
WHERE t1.date_str>="2022-05-10" AND t1.date_str<="2022-09-13"
ORDER BY t1.date_str ASC
1
2
3
4
5
6
7
8
9
10
11

利用日历表实现方式：https://blog.csdn.net/smilecall/article/details/78319242
直接创建日历表的方式搜到很多但未实际尝试过，也给出一个看起来比较合适的地址

参考：https://www.ancii.com/ankwpdeau/