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天评论数

查询效果图如下

posted @   码奴生来只知道前进~  阅读(160)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
点击右上角即可分享
微信分享提示