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