mysql 日期分组 数据补全

日期分组后 数据补0

SELECT IFNULL(countList,0) AS countList FROM (
SELECT DATE_FORMAT(ADDDATE(‘2019-01-01’,INTERVAL @d DAY),’%Y-%m-%d’) AS date,@d :=@d+1 DAY FROM 表名,(
SELECT @d :=0) temp WHERE ADDDATE(‘2019-01-01’,INTERVAL @d DAY)<=DATE_FORMAT(last_day(‘2019-01-01’),’%Y-%m-%d’) ORDER BY DAY) AS B LEFT JOIN (
SELECT DATE_FORMAT(uu.create_at,’%Y-%m-%d’) AS dateData,count(*) AS countList FROM 表名 uu WHERE DATE_FORMAT(uu.create_at,’%Y-%m’)=‘2019-01’ GROUP BY DATE_FORMAT(uu.create_at,’%Y-%m-%d’)) AS A ON A.dateData=B.date