在给定开始日期和结束日期的情况下，如何按月进行汇总

Question

我有一个类似于的表my_table

id    start_date    end_date   
1     2019-12       2021-12
2     2017-11       2019-03
3     2016-09       2018-09

我有另一张date_values表，每个日期如下：

date_value
2016-09
2016-10

我想得到每个月的产量：

month      num_people
2016-09    1
2016-10    1

我该怎么做？

我知道我需要按月份分组，然后按COUNT(id(聚合，但我不确定如何以我需要的格式获得日期。非常感谢您的帮助，谢谢！

Answer 1

您可以尝试使用BETWEEN在日期范围内使用JOIN，但您的DateTime不是有效的格式，我们需要使用STR_TO_DATE函数将字符串转换为DateTime，然后比较

查询1：

SELECT t2.date_value,COUNT(*) num_people
FROM my_table t1
INNER JOIN date_values t2
ON STR_TO_DATE(CONCAT(t2.date_value,'-01'), '%Y-%m-%d')
BETWEEN STR_TO_DATE(CONCAT(t1.start_date ,'-01'), '%Y-%m-%d')
AND STR_TO_DATE(CONCAT(t1.end_date ,'-01'), '%Y-%m-%d')
GROUP BY t2.date_value

结果：

| date_value | num_people |
|------------|------------|
|    2016-09 |          1 |
|    2016-10 |          1 |

我建议date_value、start_date、end_date可能是DateTime类型，而不是字符串类型。

如果您想以yyyy-MM格式显示DATE_FORMAT数据，我们可以使用DATE_FORMAT函数。

SELECT DATE_FORMAT(now(),'%Y-%m')