MYSQL中用sql语句按照天数统计最近7天(或者十天,一个月)的报表,如果当天没有数据则填充0
需要分析:
按照天数统计每天的数据总和,如果其中有几天没有数据,那么group by 返回会忽略那几天,如何填充为0呢?
如下图,按照需求打算统计2020年01月25日~2020年02月03日的数据,用于图表展示,但只有2月2日和2月3日有数据,其他的都没有,非常不符合报表统计的需求。
解决方案:
我们用一组连续天数的表作为左表,然后左连接left join到要查询的数据表,最后group by连续天数表的日期字段
1、连续天数表
假如要统计最近7天,那么需要找一张有7条以上固定不变数据的表,用来参照结构。要统计10天,就找有10条数据以上的,以此类推。(补充:不要找那种有几千上万条数据的,会影响查询速度,找条数差不多的就可以了)
/*连续天数表*/
(SELECT
date_format(@cdate := DATE_ADD(@cdate, INTERVAL - 1 DAY),'%Y-%m-%d') as days
FROM
(SELECT @cdate := DATE_ADD(NOW(), INTERVAL + 1 DAY) FROM `tb_menu`) t0
LIMIT 10)
SQL分析:
1.@cdate := 是定义名为cdate的变量并赋值(select 后面必须用:=)
2.@cdate := DATE_ADD(NOW(), INTERVAL + 1 DAY) 按照今天的日期,加一天
3.SELECT @cdate := DATE_ADD(NOW(), INTERVAL + 1 DAY) FROM `tb_menu` 找一张表记录肯定大于10条的即可,代码中的tb_menu就是我找的一张要参照的表,里面有20条固定数据,符合要求。
4.@cdate := DATE_ADD(@cdate, INTERVAL - 1 DAY) DAY 把定义的cdate变量天数-1(自减)
5.LIMIT 10 限制条数,最终得到了指定日期往前10天的记录
执行结果如下:
2、 左关联数据表,然后分组查询
先看一下业务数据表tb_order的内容:
按照左表关联业务数据,根据左表的日期分组,即分成了指定的10天数据,有记录就统计条数,没有记录就是0,具体代码如下:
SELECT t1.days,COUNT(t2.id) as num,
(CASE WHEN SUM(t2.money) IS NOT NULL THEN SUM(t2.money) ELSE 0 END) AS money
FROM
/*连续天数表*/
(SELECT
date_format(@cdate := DATE_ADD(@cdate, INTERVAL - 1 DAY),'%Y-%m-%d') as days
FROM
(SELECT @cdate := DATE_ADD(NOW(), INTERVAL + 1 DAY) FROM tb_menu) t0
LIMIT 10) t1
/*左连接数据表*/
LEFT JOIN
(select id,money, date_format(creatTime,'%Y-%m-%d') as days from tb_order
where `status`=1)t2
ON t2.days = t1.days
GROUP BY t1.days;
最终结果如下图:
-- 按天查
SELECT a1.days,(CASE WHEN a2.num IS NOT NULL THEN a2.num ELSE 0 END) AS num
from
(SELECT
date_format(@cdate := DATE_ADD(@cdate, INTERVAL - 1 DAY),'%m-%d') as days
FROM
(SELECT @cdate := DATE_ADD(NOW(), INTERVAL + 1 DAY) FROM `sys_resource_main`) t0
LIMIT 30) a1 left join
(SELECT DATE_FORMAT(`t_create_time`, '%m-%d') AS days,COUNT(1) AS num FROM sys_resource_main GROUP BY DATE_FORMAT(`t_create_time`, '%m-%d') ORDER BY DATE_FORMAT(`t_create_time`, '%m-%d') DESC limit 30) a2 on a1.days = a2.days
-- 按月查
SELECT a1.months as dayOrMonth,(CASE WHEN a2.num IS NOT NULL THEN a2.num ELSE 0 END) AS num
from
(SELECT
date_format(@cdate := DATE_ADD(@cdate, INTERVAL - 1 MONTH),'%Y-%m') as months
FROM
(SELECT @cdate := DATE_ADD(NOW(), INTERVAL + 1 MONTH) FROM `sys_resource_main`) t0
LIMIT 7) a1 left join
(SELECT DATE_FORMAT(`t_create_time`, '%Y-%m') AS months,COUNT(1) AS num FROM sys_resource_main GROUP BY DATE_FORMAT(`t_create_time`, '%Y-%m') ORDER BY DATE_FORMAT(`t_create_time`, '%Y-%m') DESC limit 7) a2 on a1.months = a2.months