mysql 查询统计近7天记录

有需求查询近7天 每天的访问量的需求

1：统计7天所有的访问记录 

SELECT
	DATE_FORMAT(c.addTime, '%Y-%m-%d' ) days,
	count(*) count 
FROM	
( SELECT * FROM contentMessage
 WHERE DATE_SUB( CURDATE( ), INTERVAL 7 DAY ) <= date(addTime) and  userId=5 ) as c 
GROUP BY
	days;  -- 进7天评论数

2：统计7天每天的访问量 没有的也需要记录 实现图表的数据展示需要

 SELECT curdate() as days 查询的近7天是包含当天 如不需要去掉这句代码就可以

ifnull(b.count,0) as count 是判断统计的是否为null  是的话就设置为0

SELECT * from (select a.days,ifnull(b.count,0) as count
from (
    SELECT curdate() as days
    union all
    SELECT date_sub(curdate(), interval 1 day) as days
    union all
    SELECT date_sub(curdate(), interval 2 day) as days
    union all
    SELECT date_sub(curdate(), interval 3 day) as days
    union all
    SELECT date_sub(curdate(), interval 4 day) as days
    union all
    SELECT date_sub(curdate(), interval 5 day) as days
    union all
    SELECT date_sub(curdate(), interval 6 day) as days
) a left join (
  select date(addTime) as datetime, count(*) as count
  from contentMessage where userId = 5
  group by date(addTime)
) b on a.days = b.datetime) as t ORDER BY t.days ASC;  -- 进7天评论数

查询效果图如下