1.先统计当前日期一周七天的日期
select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) + 0 DAY) as weekday
UNION
select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) - 1 DAY)
UNION
select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) - 2 DAY)
UNION
select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) - 3 DAY)
UNION
select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) - 4 DAY)
UNION
select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) - 5 DAY)
UNION
select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) - 6 DAY)
2. 然后统计每天的数量
SELECT
b.*,
@rownum := @rownum + 1 AS week
FROM
(
SELECT
a.weekday,
COUNT( nm.id ) count
FROM
(
SELECT
date_sub( curdate( ), INTERVAL WEEKDAY( curdate( ) ) - 0 DAY ) AS weekday UNION
SELECT
date_sub( curdate( ), INTERVAL WEEKDAY( curdate( ) ) - 1 DAY ) UNION
SELECT
date_sub( curdate( ), INTERVAL WEEKDAY( curdate( ) ) - 2 DAY ) UNION
SELECT
date_sub( curdate( ), INTERVAL WEEKDAY( curdate( ) ) - 3 DAY ) UNION
SELECT
date_sub( curdate( ), INTERVAL WEEKDAY( curdate( ) ) - 4 DAY ) UNION
SELECT
date_sub( curdate( ), INTERVAL WEEKDAY( curdate( ) ) - 5 DAY ) UNION
SELECT
date_sub( curdate( ), INTERVAL WEEKDAY( curdate( ) ) - 6 DAY )
) AS a
LEFT JOIN event_report AS nm ON date_format( nm.apply_time, '%Y-%m-%d' ) = a.weekday
GROUP BY
a.weekday
) b,
( SELECT @rownum := 0 ) r