如何按月汇总给定的开始日期和结束日期？

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

我该怎么做呢？

我知道我需要按月分组，然后按计数(Id)进行聚合，但我不知道如何以所需的格式获得日期。会很感激你的帮助，谢谢！

Answer 1

您可以尝试在日期范围上使用JOIN，使用BETWEEN，但是您的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类型，而不是string类型。

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

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