SQL查询，用groupby子句计算不同列中的行数

Question

在Mysql数据库中，我有两列，分别存储进程的开始日期和结束日期。我需要编写一个查询，使我能够统计每个列中每个月的行数，并单独显示计数。

表示例：

+----+------------+----------------+
| id | startData  |     endData    |
+----+-------------+----------------+
|  1 | 02/03/2020 |    02/03/2020  | 
|  2 | 02/04/2020 |    02/04/2020  |
|  3 | 02/04/2020 |    02/05/2020  |
|  4 | 02/04/2020 |    02/05/2020  |
|  5 | 02/05/2020 |    02/06/2020  |
|  6 | 02/05/2020 |    02/06/2020  |
|  7 | 02/06/2020 |    02/07/2020  |
+----+-------------+----------------+

我想要的结果：

+-------+--------------------+-------------------+
| month |  count_month_start |  count_month_end  |
+-------+--------------------+-------------------+
|  03   |         01         |          01       |
|  04   |         03         |          01       |
|  05   |         02         |          02       |
|  06   |         01         |          02       |
|  07   |         00         |          01       |
+-------+--------------------+-------------------+

Answer 1

假设开始日期和结束日期列的数据类型为date，则可以执行-

Select ifnull(Tb1.mn,Tb2.mn) As mn, ifnull(count_mn_start,0) As count_mn_start, ifnull(count_mn_end,0) As count_mn_end
from
(Select Month(StartDate) as mn, count(id) as count_mn_start
from
my_table
Group by Month(StartDate))Tb1
left Join (Select Month(EndDate) as mn, count(id) as count_mn_end
from my_table
Group by Month(EndDate)) Tb2
on Tb1.mn = Tb2.mn
UNION
Select ifnull(Tb1.mn,Tb2.mn) As mn, ifnull(count_mn_start,0) As count_mn_start, ifnull(count_mn_end,0) As count_mn_end
from
(Select Month(StartDate) as mn, count(id) as count_mn_start
from
my_table
Group by Month(StartDate))Tb1
Right Join (Select Month(EndDate) as mn, count(id) as count_mn_end
from my_table
Group by Month(EndDate)) Tb2
on Tb1.mn = Tb2.mn;

DB小提琴-https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=84ecddb9f5ed431 ddff6a9eaab87e5df

附言：如果你的日期只有一年(以2020年为例(，这是可行的，但理想情况下，如果你的数据中有不同的年份，也可以考虑在输出中有年份，在这种情况下，使用相同的语法，即年份(日期字段(，并将其添加到选择中，并在子查询中分组(与上述查询中的月份相同(。

Answer 2

一个非常简单的方法是使用递归CTE将时间段扩展为天。然后只是聚合：

with recursive cte as (
select id, startdate as dte, enddate
from t
union all
select id, dte + interval 1 day, enddate
from cte
where dte < enddate
)
select year(dte), month(dte),
sum( day(dte) = 1 ) as cnt_start,
sum( day(dte) = day(last_day(dte)) ) as cnt_end
from cte
group by year(dte), month(dte) ;

这里有一个db<gt；不停摆弄