先上代码:
SELECT lefttable.date,IFNULL(righttable.m,'0') as total
FROM
(SELECT date_sub(CURDATE(),interval @i:=@i+1 day) as date
from (select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1) as tmp,(select @i:= -1) t) as lefttable
LEFT JOIN
(SELECT count(*)as m,DATE_FORMAT(tab2.CREATED_DATE,'%Y-%m-%d') as gptime
from tb1 tab2
where tab2.DOCTOR_ID=#{doctorId} and DATE(tab2.CREATED_DATE)>=DATE_SUB(CURDATE(), INTERVAL 29 DAY) GROUP BY gptime)
as righttable
ON lefttable.date=righttable.gptime ORDER BY lefttable.date;
思路:左边表格用了29个select 1 union all 作为临时表格来辅助获得近一个月的日期,右边表格查询近一个月的数据,然后进行联合查询,从而获得近一个月每天的数量。查询结果如下:
如果只要月-日,不要年份,只需要把开头的lefttable.date换成 DATE_FORMAT(lefttable.date,'%m-%d')as date即可,
如果要查询近半个月每天的数量,只要保留14个select 1 union all,同时把SQL中的29换成14即可。