1.查询最近7天的数据
SELECT * FROM t_record
where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(createdtime)
ORDER BY createdtime desc
2.查询最近7天内有数据的天数内,每天多少条数据
select date_format(createdtime,'%Y-%m-%d') as data,
count(1) as count
from t_record
where createdtime >= date(now()) - interval 7 day
group by day(createdtime);
返回结果,只包含有数据的天数
3.统计最近7天每天数据数量,没有数据则利用ifnull 补0
SELECT a.date ,IFNULL(b.count,0) as count
FROM(
SELECT CURDATE() as date
UNION ALL
SELECT DATE_SUB(CURDATE(),INTERVAL 1 day)as date
UNION ALL
SELECT DATE_SUB(CURDATE(),INTERVAL 2 day)as date
UNION ALL
SELECT DATE_SUB(CURDATE(),INTERVAL 3 day)as date
UNION ALL
SELECT DATE_SUB(CURDATE(),INTERVAL 4 day)as date
UNION ALL
SELECT DATE_SUB(CURDATE(),INTERVAL 5 day)as date
UNION ALL
SELECT DATE_SUB(CURDATE(),INTERVAL 6 day)as date
)a LEFT JOIN(
select date_format(createdtime,'%Y-%m-%d') as date,
count(1) as count
from t_record
where createdtime >= date(now()) - interval 7 day
group by day(createdtime)
)b on a.date = b.date
ORDER BY a.date desc