本文共 1394 字,大约阅读时间需要 4 分钟。
在项目开发中,需要根据日期生成最近一周或者一个月的用户使用情况。如何根据日期直接分组就会导致某个日期没有数据,导致统计时日期缺失。
比如我要统计近7天的数据,根据日期分组统计,只能统计到有数据的日期记录
SELECT DATE_FORMAT(create_time, '%y-%m-%d') createtime, count(1) totalFROM sys_cus where del_flag = 0and DATE_SUB(date_format(NOW(),'%Y-%m-%d'),INTERVAL 7 DAY) < date_format(NOW(),'%Y-%m-%d')GROUP BY createtime
统计结果:
通过一个时间临时表来进行左连接关联。对记录为空的进行0补位。
select dts.createtime,IF (ps.total is NULL ,0,ps.total) toatal from (select @num := @num+1 rownum, date_format(DATE_ADD(DATE_SUB(date_format(NOW(),'%y-%m-%d'),INTERVAL 7 DAY),INTERVAL @num DAY),'%y-%m-%d') createtime from sys_cus , (select @num:=0) t where @num < 7 ) dts LEFT JOIN (SELECT DATE_FORMAT(create_time, '%y-%m-%d') dfc, count(1) total FROM sys_cus o where del_flag = 0 GROUP BY dfc )as ps ON ps.dfc = dts.createtime ORDER BY dts.createtime
统计结果:
@num 是mysql中的定义的变量。
@num := @num+1 相当于orace中的rownum ,伪劣。
(select @num:=0) t 初始 @num 为0
@num < 7 固定 列的大小。
上面的根据自己的数据库表,生成一个日期临时表,还是会有问题。只能根据表中的记录数来生成日期天数,如果数据小于所需要的日期天数,那就只能生成一部分日期天数。
select @num := @num+1 rownum, date_format(DATE_ADD(DATE_SUB(date_format(NOW(),'%y-%m-%d'),INTERVAL 20 DAY),INTERVAL @num DAY),'%y-%m-%d') createtime from mysql.help_topic , (select @num:=0) t where @num < 20
将上面这部分的sql代吗,将sys_cust替换为数据库的自带表mysql.help_topic做了替代。
有更好的解决办法欢迎评论区给出建议。
转载地址:http://mbfoi.baihongyu.com/