Mysql日期分组无数据查询填充0

前言

这篇文章标题不好取。。。(主要是生成连续的日期)，本文关键点有：Mysql 获取指定时间段内的所有日期列表，
Mysql 按照日期分组查询没有数据的日期也一并查询出来。

问题

产品提出一个需求，需要展示这样的一张折线图，用来反映指定时间段内网站注册用户的增加趋势，于是需要后端的 JSON 工程师给出对应的接口。

疏忽大意

具体的表结构和数据是这样的

JSON 工程师不加思索，展开了 CRUD 大法，顺手写下了一个 SQL，不到5分钟，接口完活，测都没测试直接给到了前端开发。

select date(t.create_time) as `date`,
       count(t.id)  as num
from t_user t
group by `date`;

前端拿到数据后开始绘图，结果画的图完全不对啊，因为时间不是连续的。于是反馈到了 JSON 工程师这里。

JSON 工程师一想，哎哟，没考虑掉某天没有数据的情况，分组查询的话，肯定缺少这一天的数据的。

+------------+-----+
|    date    | num |
+------------+-----+
| 2019-05-06 |  2  |
+------------+-----+
| 2019-05-08 |  2  |
+------------+-----+
| 2019-05-09 |  9  |
+------------+-----+

正确的做法应该是即使某天没有数据，也填充一个 0 作为记录值。

修正查询数据

找到了问题就好办了，捋了一下逻辑分成了两步

一、拿到所有日期

2019年09月12日更新

感谢评论区中 @一个小可爱（感觉是个有趣的人哈哈）提出的方法，于是我就来更新一下本文了。最好的方式是在 Java 代码中处理生成连续的日期，然后创建一个 map 对象，初始化所有的键值对的值为 0，然后遍历查询出来的按照日期作为 key，之后进行 put 覆盖默认值即可。

提供一个生成连续日期的方法

public static Set<String> getBetweenDate(String start, String end) {
  LocalDate startDate = LocalDate.parse(start);
  LocalDate endDate = LocalDate.parse(end);
  long between = ChronoUnit.DAYS.between(startDate, endDate);
  if (between < 1) {
    return Stream.of(start, end).collect(Collectors.toSet());
  }

  return Stream.iterate(startDate, e -> e.plusDays(1))
    .limit(between + 1)
    .map(LocalDate::toString)
    .collect(Collectors.toSet());
}


public static void main(String[] args) {
  Set<String> days = BaseTest.getBetweenDate("2019-08-29", "2019-09-02");
  log.info("{}", days);
}

二、按照日期分组查询

原来的文章可能会造成误解，已更新掉
按照日期分组查询，遍历查询结果，覆盖第一步里的时间map的值即可。

结果如下：

+------------+-----+
|    date    | num |
+------------+-----+
| 2019-05-03 |  0  |
+------------+-----+
| 2019-05-04 |  0  |
+------------+-----+
| 2019-05-05 |  0  |
+------------+-----+
| 2019-05-06 |  2  |
+------------+-----+
| 2019-05-07 |  0  |
+------------+-----+
| 2019-05-08 |  2  |
+------------+-----+
| 2019-05-09 |  9  |
+------------+-----+

顺利达到了目的，收工！

参考

• https://www.cnblogs.com/zhengguangpan/p/10308886.html

• https://blog.csdn.net/Dai_Aixy/article/details/83144619

• https://www.cnblogs.com/dennyzhangdd/p/8073181.html