mysql 统计最近七天的一星期数据

原创于 2020-10-16 10:34:38 发布 · 1.3k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #java

mysql 专栏收录该内容

13 篇文章

订阅专栏

本文介绍如何使用SQL查询MySQL，统计当前日期前七天的事件计数，通过LEFT JOIN获取sys_evente表中与position_type=#{type}

mysql 统计最近七天的数据
当前时间的前七天数据

select DATE_FORMAT(mycreatedate,'%Y-%m-%d') as 	name,ifnull(COUNT(pv.event_time), 0) as count from (
SELECT curdate() as mycreatedate
union all
SELECT date_sub(curdate(), interval 1 day) as mycreatedate
union all
SELECT date_sub(curdate(), interval 2 day) as mycreatedate
union all
SELECT date_sub(curdate(), interval 3 day) as mycreatedate
union all
SELECT date_sub(curdate(), interval 4 day) as mycreatedate
union all
SELECT date_sub(curdate(), interval 5 day) as mycreatedate
union all
SELECT date_sub(curdate(), interval 6 day) as mycreatedate
) as total
LEFT JOIN (select  e.event_time from sys_event e LEFT JOIN sys_applets a on 		e.openid=a.openid where a.position_type=#{type}) pv
on DATE_FORMAT(pv.event_time,'%Y-%m-%d')=total.mycreatedate GROUP BY mycreatedate ORDER BY mycreatedate asc