MySQL 查询每一天的日期

MySQL 查询每一天的日期

这个查询是为了查询一周的每一天的日期，或者是一个月的每一天，本是为了统计本周每天的数据或本月每天的数据

SELECT

date_format(@cdate := DATE_ADD(@cdate, INTERVAL - 1 DAY),'%Y-%m-%d') as Days  ,TO_DAYS(@cdate) SendingDay

FROM  (SELECT @cdate := DATE_ADD(SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')-7), INTERVAL + 1 DAY) FROM Course) t0  LIMIT 7

SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')-7) 这个是查询当前周的最后一天，如果要查询当前月的最后一天就改为

select last_day(curdate())

完整的查询

SELECT (CASE WEEKDAY(t1.wk)
    WHEN 0 THEN '周一'
    WHEN 1 THEN '周二'
    WHEN 2 THEN '周三'
    WHEN 3 THEN '周四'
    WHEN 4 THEN '周五'
    WHEN 5 THEN '周六'
    WHEN 6 THEN '周日'
END) as wk
,t2.cc FROM (
SELECT date_format(@cdate := DATE_ADD(@cdate, INTERVAL - 1 DAY),'%Y-%m-%d') as wk    
FROM  (SELECT @cdate := DATE_ADD(SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')-7), INTERVAL + 1 DAY) FROM Course) t0
LIMIT 7  ) t1 
LEFT JOIN (SELECT date_format(evdate,'%Y-%m-%d') as df, count(1) as cc FROM Eva ev
WHERE  schoolid='1' AND YEARWEEK(date_format(evdate, '%Y-%m-%d')) = YEARWEEK(CURDATE())
GROUP BY WEEKDAY(evdate) ) t2 ON t1.wk=t2.df
ORDER BY t1.wk