查询最近七天的数据统计

select COUNT(0) count, DATE_FORMAT(operateTime,'%m-%d') time, 
case DAYOFWEEK(operateTime)  when 1 then '星期日' when 2 then '星期一' when 3 then '星期二' when 4 then '星期三' when 5 then '星期四' when 6 then '星期五' when 7 then '星期六' end  week
from rm_ferrydata_log 
where datediff(now(),operateTime)<=6  
group by time
ORDER BY time;

这样查询，如果这周的数据没记录，ze无数据查出，‘

优化： null 转0， 没有数据，就显示时间，且数量设为0

SELECT IFNULL(b.count,0) count, a.click_date time,
case DAYOFWEEK(click_date)  when 1 then '星期日' when 2 then '星期一' when 3 then '星期二' when 4 then '星期三' when 5 then '星期四' when 6 then '星期五' when 7 then '星期六' end  week
from (
    SELECT curdate() as click_date
    union all
    SELECT date_sub(curdate(), interval 1 day) as click_date
    union all
    SELECT date_sub(curdate(), interval 2 day) as click_date
    union all
    SELECT date_sub(curdate(), interval 3 day) as click_date
    union all
    SELECT date_sub(curdate(), interval 4 day) as click_date
    union all
    SELECT date_sub(curdate(), interval 5 day) as click_date
    union all
    SELECT date_sub(curdate(), interval 6 day) as click_date
) a 
left join (
	select COUNT(0) count, date(operateTime) time
	from rm_ferrydata_log 
	where  datediff(now(),operateTime)<=6  
	group by time
) b
on a.click_date=b.time