mysql查询最近7天的数据？

1.只显示有记录的数据

SELECT 
	DATE_FORMAT(create_time,"%Y-%m-%d") as createtimes,
	COUNT(*) as nums
	FROM USER 
	where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(create_time) 
	GROUP BY DATE_FORMAT(create_time,"%Y-%m-%d")
	ORDER BY DATE_FORMAT(create_time,"%Y-%m-%d") DESC
1
2
3
4
5
6
7

结果：只会记录有值的

2.要求显示每一天的数据量，为空则填充0

select a.click_date,ifnull(b.nums,0) as count
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 
	DATE_FORMAT(create_time,"%Y-%m-%d") as createtimes,
	COUNT(*) as nums
	FROM `user` 
	where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(create_time) 
	GROUP BY DATE_FORMAT(create_time,"%Y-%m-%d")
	ORDER BY DATE_FORMAT(create_time,"%Y-%m-%d") DESC
) b on a.click_date = b.createtimes ORDER BY a.click_date asc

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

结果：