sql语句:
select day_list.tdate as day ,IFNULL(sto.Num,0) as createNum ,IFNULL(sto1.Num,0) as finishNum from
(
SELECT @cdate := date_add( @cdate, INTERVAL -1 DAY ) tdate
FROM ( SELECT @cdate := date_add( CURDATE(), INTERVAL 1 DAY ) FROM sys_task LIMIT 10 ) days
) day_list
LEFT JOIN (SELECT COUNT(id) as Num ,date_format(sys_task.create_time, '%Y-%m-%d') as time from sys_task
where sys_task.del_flag = 0
and sys_task.deal_type = 0
and sys_task.belong_id = 1
GROUP BY date_format(sys_task.create_time, '%Y-%m-%d')) sto
on day_list.tdate = sto.time
LEFT JOIN(SELECT COUNT(id) as Num,date_format(sys_task.update_time, '%Y-%m-%d') as time from sys_task
where sys_task.del_flag = 0
and sys_task.deal_type = 0
and sys_task.belong_id = 1
and sys_task.deal_status in (2,4,5,8,10)
GROUP BY date_format(sys_task.update_time, '%Y-%m-%d')) sto1
on day_list.tdate = sto1.time
order by day_list.tdate
运行结果:
任务表:sys_task ,其中含有对应关键字。
功能:查询sys_task表进10天创建任务数与完成任务数(sys_task.deal_status in (2,4,5,8,10))。
表中对应日期没有数据的置0。
注意:表中数据应该大于等于所要查询的天数(例:10天要10条数据触发10次日期的修改)。
小结:不想用Union等方法创建临时日期表,感觉有点不灵活。