Mysql 实现统计最近7天用户登录人数人数为空则填充为0

Lcry

发布于 2022-11-29 19:16:54

1.4K0

发布于 2022-11-29 19:16:54

文章被收录于专栏：Lcry个人博客

最近在做毕设，然后做统计功能，简单的统计一下用户近7天的登录情况，数据库有一张t_log日志表进行记录登录，但是会遇到偶尔中间一天没有登录人数，那么要显示日期，人数填充为0，于是进行了实现了。

获取最近7天的SQL:

select a.date
from (
    SELECT curdate() as date
    union all
    SELECT date_sub(curdate(), interval 1 day) as date
    union all
    SELECT date_sub(curdate(), interval 2 day) as date
    union all
    SELECT date_sub(curdate(), interval 3 day) as date
    union all
    SELECT date_sub(curdate(), interval 4 day) as date
    union all
    SELECT date_sub(curdate(), interval 5 day) as date
    union all
    SELECT date_sub(curdate(), interval 6 day) as date
) a

然后去关联业务表，进行筛选，ifnull 函数判断是否为空，最终SQL如下：

select a.date,ifnull(b.count,0) as count
from (
    SELECT curdate() as date
    union all
    SELECT date_sub(curdate(), interval 1 day) as date
    union all
    SELECT date_sub(curdate(), interval 2 day) as date
    union all
    SELECT date_sub(curdate(), interval 3 day) as date
    union all
    SELECT date_sub(curdate(), interval 4 day) as date
    union all
    SELECT date_sub(curdate(), interval 5 day) as date
    union all
    SELECT date_sub(curdate(), interval 6 day) as date
) a LEFT join 
(select DATE_FORMAT(create_time,'%Y-%m-%d') days,count(*) count from t_log group by days) b
on a.date=b.days order by a.date

其中 t_log 为日志表，create_time是其中一个字段

参考链接： https://blog.csdn.net/ljxbbss/article/details/78028424 https://www.cnblogs.com/phperlinxinlan/p/10528859.html https://blog.csdn.net/LONG_Yi_1994/article/details/85705047 https://blog.csdn.net/dmw412724/article/details/95318091

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2019-07-28，如有侵权请联系 cloudcommunity@tencent.com 删除

https