前言

这篇文章标题不好取。。。本文关键点有:Mysql 获取指定时间段内的所有日期列表,
Mysql 按照日期分组查询没有数据的日期也一并查询出来。

问题

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

疏忽大意

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

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

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

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

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

1
2
3
4
5
6
7
8
9
+------------+-----+
| date | num |
+------------+-----+
| 2019-05-06 | 2 |
+------------+-----+
| 2019-05-08 | 2 |
+------------+-----+
| 2019-05-09 | 9 |
+------------+-----+

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

修正查询数据

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

一、拿到所有日期

因为入参有两个,分别是起始日期与结束日期,需要拿到两个日期之间的所有日期,从而按照日期分组。

1
2
3
4
5
6
7
8
select * from (SELECT DATE_FORMAT(DATE_SUB('2019-05-09', INTERVAL xc day), '%Y-%m-%d') as day
FROM (SELECT @xi := @xi + 1 as xc
from (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) xc1,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) xc2,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) xc3,
(SELECT @xi := -1) xc0
) as t) as t2
where t2.`day` > '2019-05-02' and t2.`day` <= '2019-05-09'

结果如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
+------------+
| DAY |
+------------+
| |
| 2019-05-09 |
| |
| 2019-05-08 |
| |
| 2019-05-07 |
| |
| 2019-05-06 |
| |
| 2019-05-05 |
| |
| 2019-05-04 |
| |
| 2019-05-03 |
+------------+

二、联合用户表查询

拿到所有日期后作为一张左表,join 连接用户表,再进行分组查询

1
2
3
4
5
6
7
8
9
10
11
12
select t2.`day` as `date`, count(t.id) as num
from (SELECT DATE_FORMAT(DATE_SUB('2019-05-09', INTERVAL xc day), '%Y-%m-%d') as day
FROM (
SELECT @xi := @xi + 1 as xc
from (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) xc1,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) xc2,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) xc3,
(SELECT @xi := -1) xc0
) as x1x2x) t2
left join t_user t on date(t.create_time) = t2.day
where t2.`day` > '2019-05-02' and t2.`day` <= '2019-05-09'
group by date ORDER BY date asc;

结果如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
+------------+-----+
| 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 |
+------------+-----+

顺利达到了目的,收工!

参考