最近在做毕设,然后做统计功能,简单的统计一下用户近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

扫描下面二维码,给博主打赏点吧~

微信

微信

支付宝

支付宝


作者:Lcry
本文链接:https://www.51it.wang/ll/1603
商业转载请联系作者获得授权,非商业转载请注明本文出处及文章链接

带符号 * 的表示必填项