mysql获取近7天每天数据，无数据补0(sql详细用法)

1.首先裁分出7天

select a.click_date
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 

然后在把需要查询7内数据查询出来

create_time 为创建时间
nums 为条数

SELECT 
	DATE_FORMAT(create_time,"%Y-%m-%d") as createtimes,
	COUNT(*) as nums
	FROM ad_meta_data 
	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

最终拼接在一起
查询7内数据sql 放在left join()里
用左右表连接的方式就是时间对时间

a 表时间 对上 真正数据表时间
on a.click_date = b.createtimes;
b表的条数为null 就补0
,ifnull(b.nums,0) as count

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 ad_meta_data 
	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;