MySQL - 计算通用值是否介于

Question

这是我的 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 中可以吗？

Answer 1

最棘手的部分是生成数字。剩下的就是 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;