mysql按照天数统计最近7天（或者十天，一个月）的报表，如果当天没有数据则填0

转载至:https://blog.csdn.net/qq15577969/article/details/104155299

需要分析：
按照天数统计每天的数据总和，如果其中有几天没有数据，那么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;