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