Mysql查询数据日期不连续补全的问题

场景：同学问我要做个报表的折线图统计日期金额 数据库MYSQL 于是随便找个表写个例子

SELECT  SUM(realtax), DATE_FORMAT(createdTime,'%Y-%m-%d') as date FROM `wage_monthwage`WHERE createdTime BETWEEN '2019-03-11' and '2019-08-16' GROUP BY date

image.png

但是日期不连续，虽然前端控件可以自动补0但是还是想找个后端的解决方案以后可能会用到

参考：https://www.cnblogs.com/qq1871707128/p/8603090.html
的日期补全结合

SELECT a.num,a.date,IFNULL(b.count,0),b.date FROM((select @num:=@num+1 as num,date_format(adddate('2019-03-11', INTERVAL @num DAY),'%Y-%m-%d') as date
from wage_monthwage,(select @num:=0) t where adddate('2019-03-11', INTERVAL @num DAY) <= date_format('2019-09-16','%Y-%m-%d')
order by date
))a 
LEFT JOIN (SELECT SUM(realtax) as count, DATE_FORMAT(createdTime,'%Y-%m-%d') as date FROM `wage_monthwage` WHERE createdTime BETWEEN '2019-03-11' and '2019-08-16' GROUP BY date)b
on a.date = b.date

image.png image.png