sql count为空时显示0_SQL将一行展开为多行

本文由一个朋友问的问题简化而来。问题是——

假如有一个活动表(activity)，它有两个日期字段 start 和 end，分别表示活动开始日期和结束日期。现要求，在创建活动时，任一日期内总的活动数不得超过2个。那么，怎么判断这个条件？

问题解释

假设 activity 表已经有如下2条记录：

mysql> select * from activity;
+----+------------+------------+
| id | start      | end        |
+----+------------+------------+
|  1 | 2020-01-01 | 2020-01-03 |
|  2 | 2020-01-01 | 2020-01-02 |
+----+------------+------------+
2 rows in set (0.01 sec)

当我们继续添加开始日期为 2020-01-01 的记录时，将不再被允许，因为已经有2条相同日期的活动了。同理，不能再出现包含了 2020-01-02 日期的活动，而含有 2020-01-03 日期的活动还能添加一条。

解决方案

仔细想来，由于每个日期的活动只允许最多出现2条，我们需将数据按日期分组，统计每个日期下已经有多少活动。但每一行数据的日期区间可能包含多条日期，所以需要将区间内所有日期都展开，即，把一行数据展开为多行。展开后类似下面这样：

+------------+----+------------+------------+
| date       | id | start      | end        |
+------------+----+------------+------------+
| 2020-01-01 |  1 | 2020-01-01 | 2020-01-03 |
| 2020-01-02 |  1 | 2020-01-01 | 2020-01-03 |
| 2020-01-03 |  1 | 2020-01-01 | 2020-01-03 |
| 2020-01-01 |  2 | 2020-01-01 | 2020-01-02 |
| 2020-01-02 |  2 | 2020-01-01 | 2020-01-02 |
+------------+----+------------+------------+
5 rows in set, 1 warning (0.00 sec)

之后，对 date 列进行 group汇总 即可。

问题核心将变成，怎么将一行转换为多行？

熟悉sql的童鞋大概已经想到，利用 join 呗！用一张多行的表 join 一张单行的表，很容易得到多行数据。因此，观察上面表格的 date 列，我们只需要一张预存有日期序列的常量表，把这张表与活动表联合起来，问题将得以解决。

日期序列的常量表

该表(假设名为 cal)内容如下：

mysql> select * from cal;
+------------+
| date       |
+------------+
| 2020-01-01 |
| 2020-01-02 |
| 2020-01-03 |
+------------+
3 rows in set (0.01 sec)

(注意，根据你公司的存活时间，可以将date存远一些，比如一直存到3030-01-01)。

将两张表联合起来

mysql> select c.date,a.* from cal c join activity a
    -> on c.date>=a.start and c.date<=a.end
    ->
    -> where c.date>='2020-01-01' and '2020-01-03'
    -> order by id asc;
+------------+----+------------+------------+
| date       | id | start      | end        |
+------------+----+------------+------------+
| 2020-01-01 |  1 | 2020-01-01 | 2020-01-03 |
| 2020-01-02 |  1 | 2020-01-01 | 2020-01-03 |
| 2020-01-03 |  1 | 2020-01-01 | 2020-01-03 |
| 2020-01-01 |  2 | 2020-01-01 | 2020-01-02 |
| 2020-01-02 |  2 | 2020-01-01 | 2020-01-02 |
+------------+----+------------+------------+
5 rows in set, 1 warning (0.01 sec)

按日期汇总

假如想要新建一个日期区间为 [2020-01-01,020-01-03] 的活动。汇总查询的sql如下：

select c.date,count(1) from cal c join activity a
on c.date>=a.start and c.date<=a.end

where c.date>='2020-01-01' and '2020-01-03'

group by c.date;

得到的结果：

+------------+----------+
| date       | count(1) |
+------------+----------+
| 2020-01-01 |        2 |
| 2020-01-02 |        2 |
| 2020-01-03 |        1 |
+------------+----------+
3 rows in set, 1 warning (0.01 sec)

我们看到，已经有2个日期包含2条活动了，因此不能再添加该日期区间的活动了。

One SQL to rule them all

最终判断条件的SQL可以这样写：

select 1 as has from cal c join activity a
on c.date>=a.start and c.date<=a.end

where c.date>='2020-01-01' and '2020-01-03'

group by c.date
having count(1) >=2
limit 1

当返回的结果集为空时，说明可以添加；否则不能添加。

---

如果你不想使用常量表，可以按需生成临时表，比如利用union：

mysql> select '2020-01-01' as date
    -> union
    -> select '2020-01-02'
    -> union
    -> select '2020-01-03';
+------------+
| date       |
+------------+
| 2020-01-01 |
| 2020-01-02 |
| 2020-01-03 |
+------------+
3 rows in set (0.01 sec)

用这张临时表去 join 活动表。

---

总结，类似的需求，当需要将一行转换为多行时，可以考虑借助常量表结合join来处理。