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