mysql按时间分组排序 没有的补0

参考 https://blog.csdn.net/qq_39314099/article/details/90667087
https://www.cnblogs.com/sung1024/p/11037494.html

SELECT data.day,IFNULL(data.count, 0), day_list.day as date from 
(select DATE_FORMAT(DS_APPLY_VERITY_TIME, '%Y-%m-%d') day, count(DS_APPLY_ID) count from ds_apply GROUP BY day) data
right join 
(SELECT @date := DATE_SUB(@date, interval 1 DAY) day from 
(SELECT @date := DATE_SUB(DATE_FORMAT(now(), '%Y-%m-%d'), interval -1 DAY) from ds_apply)
 days limit 28) day_list on day_list.day = data.day

结果图

当天时间

SELECT data.day,IFNULL(data.count, 0), day_list.day1 as date from 
(select DATE_FORMAT(DS_APPLY_VERITY_TIME, '%Y-%m-%d:%H') day, count(DS_APPLY_ID) count from ds_apply GROUP BY day) data
right join 
(SELECT @date := DATE_FORMAT(DATE_SUB(@date, interval 1 HOUR),'%Y-%m-%d:%H') day1 from 
(SELECT @date := DATE_SUB(DATE_FORMAT(NOW(), '%Y-%m-%d-%H'), interval -1 HOUR) from ds_apply)
 days limit 24) day_list on day_list.day1 = data.day