MySQL中计算两个日期间每月1日、15日的出现次数

方法一：递归CTE（MySQL 8.0+ 推荐） #

这种方法通过生成所有需要检查的目标日期（每月1日和15日），再关联数据表统计符合条件的日期数量，逻辑直观且易维护。

统计全表所有记录的总次数 #

假设你的表名为your_table，包含start_date和end_date字段：

WITH RECURSIVE target_dates AS (
    -- 从表中最早的起始日期所在月的1日开始生成目标日期
    SELECT DATE_FORMAT(MIN(start_date), '%Y-%m-01') AS target_date
    FROM your_table
    UNION ALL
    SELECT
        CASE 
            WHEN DAY(target_date) = 1 THEN DATE_ADD(target_date, INTERVAL 14 DAY) -- 1日之后生成当月15日
            ELSE DATE_ADD(DATE_FORMAT(target_date, '%Y-%m-01'), INTERVAL 1 MONTH) -- 15日之后生成下月1日
        END AS target_date
    FROM target_dates
    WHERE target_date <= (SELECT MAX(end_date) FROM your_table) -- 生成到表中最晚的结束日期为止
)
-- 关联数据表，统计所有落在记录时间范围内的目标日期数量
SELECT COUNT(*) AS total_count
FROM your_table t
JOIN target_dates td ON td.target_date BETWEEN t.start_date AND t.end_date;

统计单条记录的次数（以你提供的示例日期为例） #

WITH RECURSIVE target_dates AS (
    -- 生成起始日期之后的第一个目标日期
    SELECT 
        CASE 
            WHEN DAY('2021-07-28') > 15 THEN DATE_FORMAT(DATE_ADD('2021-07-28', INTERVAL 1 MONTH), '%Y-%m-01')
            WHEN DAY('2021-07-28') > 1 THEN DATE_FORMAT('2021-07-28', '%Y-%m-15')
            ELSE DATE_FORMAT('2021-07-28', '%Y-%m-01')
        END AS target_date
    UNION ALL
    SELECT
        CASE 
            WHEN DAY(target_date) = 1 THEN DATE_ADD(target_date, INTERVAL 14 DAY)
            ELSE DATE_ADD(DATE_FORMAT(target_date, '%Y-%m-01'), INTERVAL 1 MONTH)
        END AS target_date
    FROM target_dates
    WHERE target_date <= '2021-10-21'
)
SELECT COUNT(*) AS count
FROM target_dates;

执行后会返回6，与示例预期一致。

方法二：日期函数公式法（兼容MySQL 5.x） #

如果你的MySQL版本不支持递归CTE，可以用日期函数计算，通过数学逻辑直接统计次数：

SELECT 
    SUM(
        -- 计算两个日期之间完整月份的目标日期数量：每个完整月贡献2次
        2 * (
            PERIOD_DIFF(
                DATE_FORMAT(end_date, '%Y%m'),
                DATE_FORMAT(start_date, '%Y%m')
            ) - 1
        )
        -- 检查起始月份的1日是否在时间范围内
        + CASE WHEN DATE_FORMAT(start_date, '%Y-%m-01') BETWEEN start_date AND end_date THEN 1 ELSE 0 END
        -- 检查起始月份的15日是否在时间范围内
        + CASE WHEN DATE_FORMAT(start_date, '%Y-%m-15') BETWEEN start_date AND end_date THEN 1 ELSE 0 END
        -- 检查结束月份的1日是否在时间范围内
        + CASE WHEN DATE_FORMAT(end_date, '%Y-%m-01') BETWEEN start_date AND end_date THEN 1 ELSE 0 END
        -- 检查结束月份的15日是否在时间范围内
        + CASE WHEN DATE_FORMAT(end_date, '%Y-%m-15') BETWEEN start_date AND end_date THEN 1 ELSE 0 END
    ) AS total_count
FROM your_table;

边界情况说明 #

• 当start_date和end_date在同一个月时，公式会自动只统计该月内符合条件的1日/15日
• 若start_date恰好是1日或15日，会被计入统计
• 若end_date早于当月15日，只会统计当月1日（如果在范围内）

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