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

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

您可以尝试在日期范围内JOIN，使用BETWEEN，但是您的日期时间格式无效，我们需要使用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

Results :

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

我建议date_value、start_date、end_date可以是DateTime类型而不是string类型。

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

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