当前周七天
SELECT DATA
.DAY,
IFNULL( DATA.count, 0 ) AS count1,
day_list.DAY AS date
FROM
( SELECT DATE_FORMAT( r.identify_time, '%Y-%m-%d' ) DAY, count( r.identify_record ) count FROM face_identify_record r GROUP BY DAY )
DATA RIGHT JOIN (
SELECT
@date := DATE_ADD( @date, INTERVAL 1 DAY ) DAY
FROM
( SELECT @date := DATE_ADD( date_sub(curdate(),INTERVAL WEEKDAY(curdate()) + 0 DAY), INTERVAL - 1 DAY ) FROM face_identify_record r ) days
LIMIT 7
) day_list ON day_list.DAY = DATA.DAY;
最近七天(包括今天)
SELECT DATA
.DAY,
IFNULL( DATA.count, 0 ) AS count1,
day_list.DAY AS date
FROM
( SELECT DATE_FORMAT( r.identify_time, '%Y-%m-%d' ) DAY, count( r.identify_record ) count FROM face_identify_record r GROUP BY DAY )
DATA RIGHT JOIN (
SELECT
@date := DATE_ADD( @date, INTERVAL 1 DAY ) DAY
FROM
( SELECT @date := DATE_ADD( DATE_SUB(CURDATE(), INTERVAL 7 DAY), INTERVAL - 0 DAY ) FROM face_identify_record r ) days
LIMIT 7
) day_list ON day_list.DAY = DATA.DAY;
date_sub(curdate(),INTERVAL WEEKDAY(curdate()) + 0 DAY)
为当前周第一天的日期,DATE_SUB(CURDATE(), INTERVAL 7 DAY)
为七天前的日期INTERVAL
为时间间隔,date_add
和date_sub
能对指定的时间进行增加或减少一个指定的时间间隔,返回的是一个日期,两个函数增加或减少时间间隔的符号正好相反limit 7
查该天往后七天