mysql查询最近7天的数据？

1.只显示有记录的数据

SELECT 
    DATE_FORMAT(create_time,"%Y-%m-%d")as createtimes,COUNT(*)as nums
    FROMUSERwhere DATE_SUB(CURDATE(),INTERVAL7DAY)<=date(create_time)GROUPBY DATE_FORMAT(create_time,"%Y-%m-%d")ORDERBY DATE_FORMAT(create_time,"%Y-%m-%d")DESC

结果：只会记录有值的

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

select a.click_date,ifnull(b.nums,0)as count
from(SELECT curdate()as click_date
    unionallSELECT date_sub(curdate(),interval1day)as click_date
    unionallSELECT date_sub(curdate(),interval2day)as click_date
    unionallSELECT date_sub(curdate(),interval3day)as click_date
    unionallSELECT date_sub(curdate(),interval4day)as click_date
    unionallSELECT date_sub(curdate(),interval5day)as click_date
    unionallSELECT date_sub(curdate(),interval6day)as click_date
) a leftjoin(SELECT 
    DATE_FORMAT(create_time,"%Y-%m-%d")as createtimes,COUNT(*)as nums
    FROM`user`where DATE_SUB(CURDATE(),INTERVAL7DAY)<=date(create_time)GROUPBY DATE_FORMAT(create_time,"%Y-%m-%d")ORDERBY DATE_FORMAT(create_time,"%Y-%m-%d")DESC) b on a.click_date = b.createtimes ORDERBY a.click_date asc

结果：