时间戳

首页 > 解决方案 > MySQL - 如果通用值介于

mysql - MySQL - 如果通用值介于

问题描述

这是我的 MySQL 表：

+----+---------------------+---------------------+
| id | startDate           | endDate             |
+----+---------------------+---------------------+
| 1  | 2018-03-20 10:10:10 | 2018-10-02 21:44:00 |
+----+---------------------+---------------------+
| 2  | 2017-08-02 21:44:00 | 2017-08-03 11:00:00 |
+----+---------------------+---------------------+
| 3  | 2018-10-25 12:12:12 | 2018-11-25 12:22:33 |
+----+---------------------+---------------------+
| 4  | 2015-01-01 23:43:27 | 2018-12-29 22:12:35 |
+----+---------------------+---------------------

我喜欢做的是从本月开始的最后 12 个月，并检查每个月的第一天，这一天在startDate和之间有多少行endDate

预期结果（2018 年 7 月 18 日）：

+---------------------+-------+
| date                | count |
+---------------------+-------+
| 2017-08-01 00:00:00 | 1     | (id 4)
+---------------------+-------+
| 2017-09-01 00:00:00 | 1     | (id 4)
+---------------------+-------+
| 2017-10-01 00:00:00 | 1     | (id 4)
+---------------------+-------+
| 2017-11-01 00:00:00 | 1     | (id 4)
+---------------------+-------+
| 2017-12-01 00:00:00 | 1     | (id 4)
+---------------------+-------+
| 2018-01-01 00:00:00 | 1     | (id 4)
+---------------------+-------+
| 2018-02-01 00:00:00 | 1     | (id 4)
+---------------------+-------+
| 2018-03-01 00:00:00 | 1     | (id 4)
+---------------------+-------+
| 2018-04-01 00:00:00 | 2     | (id 1 and 4)
+---------------------+-------+
| 2018-05-01 00:00:00 | 2     | (id 1 and 4)
+---------------------+-------+
| 2018-06-01 00:00:00 | 2     | (id 1 and 4)
+---------------------+-------+
| 2018-07-01 00:00:00 | 2     | (id 1 and 4)
+---------------------+-------+

在MySQL中可以吗？

标签： mysqlsql

解决方案

最棘手的部分是生成数字。剩下的就是JOIN和GROUP BY：

select d.dte, count(t.startdate)
from (select date('2017-08-01') as dte union all
      select date('2017-09-01') as dte union all
      . . . 
      select date('2018-07-01') as dte
     ) d left join
     mysql_table t
     on d.dte >= t.startdate and d.dte <= t.enddate
group by d.dte
order by d.dte;

推荐阅读