mysql 判断日期间隔_MySQL中实现连续日期内数据统计缺省天数0补全

MySQL中实现连续日期内数据统计缺省天数0补全

MySQL中实现连续日期内数据统计,缺省天数0补全

昨天群里有同学在问：想要查找当前日期到前7天时间内，每天的数据量总和是多少？

这个相信很多人都可以想到直接用group by data就可以实现，但是这里有一个关键，现在需要把这七天内缺少的几天数据统计出来，因为本人也是刚工作半年多的时间，对SQL也并不是了解的特别多，上网查了一下相关问题的资料，可能是查找的不正确，或者看的不全面，网上多数博客回答的都有那么一些问题，所以自己就新建了一个用来进行测试的数据库来进行实际操作。

转入正题：下面是全过程，最后实现是在最后一步，如果只需要看到结果，可以略过前面步骤

(一)：在MySQL数据库中建了一张名为test的表

按照我们的思路，首先想到的应该是如何查询当前时间到七天前的数据？是在后台对时间进行处理，然后传到SQL中使用between date1 and date2这种方式么？会不会有更好的办法？

在这里MySQL其实还有一种更便捷的方法来快速取到当前日期到前七天的日期，这里我们使用的是表test,一下文章出现的表均为此表。

语法：

select day(createTIme) date，count(*) count from test where DATE_SUB(now(), interval 7 DAY) <= createTime group by DATE_FORMAT(createTime, '%Y-%m-%d')

这里解释一下，DATE_SUB(date,INTERVAL expr type)函数：从日期减去指定的时间间隔，date 参数是合法的日期表达式。expr 参数是您希望添加的时间间隔，type是时间类型，这里可以有day,month,year等等，还有很多其他参数，如有需要可以看一下MySQL的官方文档。

可以通过下面这个链接快速查看

这里是查询结果：

通过查询结果我们可以看到，当前日期是(2017-12-21)，按照我们的想法，查询出来的数据应该是21，20，19，18，17，16，15这七天的数据才对，但是看一下test表，从21到15这七天中，有两天(16和17)并没有数据，但是实际中，我们绘制折线图等等的时候，就算没有数据，这两天我们也需要显示成0，显然这不符合我们的实际需求。这是我们会想，可以在后端拿到数据后进行循环判断，查找出是哪几天缺少了，然后进行赋值，但是利用循环时，会降低效率，那么有没有更好的实现方式呢？

(二)：使用union关键字进行连接查询

直接上代码及查询结果：

我们看到，使用union进行连接查询之后，显示出的结果16和17号两天已经补上了数据0，但是这个时候我们会提出一个疑问，16和17号我们是在看到第一次的查询结果之后，才知道这两天的数据缺失了，在不知道的情况下怎么办呢？在这种情况下，我们第一时间想到的可能是，在后端获取到日期，然后把日期当成参数传递到SQL中，但是这样做会拼接出很多的union，会使SQL变的很长，而且当数据量大的时候怎么办呢？效率会不会很低？接下来我们来解决这个问题。

(三)：使用union all关键字进行连接查询

为了解决拼接时间带来的麻烦，这里我们就可以借助一张临时日期表，我们可以在这个日期表中使用SQL脚本产生XXXX年-YYYY年的日期，下面是临时表，在这里因为是测试，我只录入了几条数据。

表里只有两个字段，xxx任意字段，date日期。

下面我们来看一下使用union all之后的查询结果：

我们看到，想要的结果出来啦！到此为止，我们算是实现了连续日期内查询的功能，或许还有更好的方法来实现，只是目前为止我没有想到，有更好办法的可以一起交流一下。

对于union和union all在这里就不做解释了，有疑问的可以去找伟大的度娘进行查询。

MySQL中实现连续日期内数据统计缺省天数0补全相关教程