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

文章目录