MySQL中统计两个日期范围内每月3日和24日的出现次数

要搞定日期范围内每月3日和24日的出现次数统计，得先把这段时间里的所有日期生成出来，再按天数分组统计。下面是可行的SQL方案：

完整SQL代码 #

WITH RECURSIVE calendar AS (
    SELECT 
        user_id,
        start_date AS date
    FROM volunteers
    UNION ALL
    SELECT 
        c.user_id,
        DATE_ADD(c.date, INTERVAL 1 DAY) AS date
    FROM calendar c
    JOIN volunteers v ON c.user_id = v.user_id
    WHERE DATE_ADD(c.date, INTERVAL 1 DAY) <= v.end_date
)
SELECT 
    user_id,
    SUM(CASE WHEN DAY(date) = 3 THEN 1 ELSE 0 END) AS number_of_third_day,
    SUM(CASE WHEN DAY(date) = 24 THEN 1 ELSE 0 END) AS number_of_twenty_fourth_day
FROM calendar
GROUP BY user_id;

代码说明 #

• 递归日历表calendar：
  • 初始查询：从volunteers表取出每个用户的起始日期作为日历的起点。
  • 递归部分：每次在已生成的日期基础上加1天，直到日期不超过该用户的结束日期，这样就能生成完整的日期序列。
• 统计逻辑：
  • 用CASE语句判断每个日期的天数是否为3或24，符合条件就记1，否则记0，再用SUM累加次数，最后按user_id分组得到结果。

测试结果验证 #

针对你提供的测试数据：

user_id | start_date | end_date
11122   | 2017-04-20 | 2018-02-17

执行上面的SQL后，会得到你期望的输出：

user_id | number_of_third_day | number_of_twenty_fourth_day
11122   |        10           |              10

内容的提问来源于stack exchange，提问作者pty