MySQL中如何灵活自定义多周期的日期范围用于关联查询？

完全可以实现你要求的“只配置全局起止日期、选定期望统计粒度就自动生成全量日期范围”的效果，不需要逐段手写UNION拼接，也能从根源上避免手写日期的拼写错误。核心方案是利用MySQL的内置能力自动遍历生成符合规则的日期边界，只需要修改最开头的几个全局参数就能切换统计逻辑，查询主体部分完全不需要改动。

这个版本是最简洁的实现方式，直接把原来手写的dates CTE替换成递归自动生成的版本即可：

-- -------------------------- 仅需修改这里的全局参数 --------------------------
SET @global_start_date = '2020-11-01'; -- 全局统计起始日期
SET @global_end_date = '2020-12-31'; -- 全局统计结束日期
-- 统计周期类型：day=按日/week=按自然周（周一至周日）/month=按自然月/year=按自然年
SET @period_type = 'month';
-- 若需生成超过1000个周期（比如按日统计3年以上），可打开下面的配置调整递归深度
-- SET cte_max_recursion_depth = 10000;
-- ---------------------------------------------------------------------------

-- 自动生成日期范围的递归CTE，完全替代原手写UNION的日期块
WITH RECURSIVE dates_config AS (
  -- 初始值：根据选中的周期类型，计算第一个周期的准确起止边界
  SELECT
    CASE @period_type
      WHEN 'day' THEN @global_start_date
      WHEN 'week' THEN DATE_SUB(@global_start_date, INTERVAL WEEKDAY(@global_start_date) DAY) -- 固定取周一为周起始
      WHEN 'month' THEN DATE_FORMAT(@global_start_date, '%Y-%m-01') -- 固定取当月1号为月起始
      WHEN 'year' THEN DATE_FORMAT(@global_start_date, '%Y-01-01') -- 固定取当年1月1号为年起始
    END AS period_start,
    CASE @period_type
      WHEN 'day' THEN DATE_ADD(@global_start_date, INTERVAL 1 DAY)
      WHEN 'week' THEN DATE_ADD(DATE_SUB(@global_start_date, INTERVAL WEEKDAY(@global_start_date) DAY), INTERVAL 7 DAY) -- 取次周周一为周结束，即周期覆盖完整7天
      WHEN 'month' THEN DATE_ADD(DATE_FORMAT(@global_start_date, '%Y-%m-01'), INTERVAL 1 MONTH) -- 取下月1号为月结束
      WHEN 'year' THEN DATE_ADD(DATE_FORMAT(@global_start_date, '%Y-01-01'), INTERVAL 1 YEAR) -- 取次年1月1号为年结束
    END AS period_end

  UNION ALL

  -- 递归部分：逐次计算下一个周期的时间，直到超出全局结束日期范围
  SELECT
    DATE_ADD(period_start, INTERVAL
      CASE @period_type
        WHEN 'day' THEN 1 DAY WHEN 'week' THEN 7 DAY
        WHEN 'month' THEN 1 MONTH WHEN 'year' THEN 1 YEAR
      END
    ),
    DATE_ADD(period_end, INTERVAL
      CASE @period_type
        WHEN 'day' THEN 1 DAY WHEN 'week' THEN 7 DAY
        WHEN 'month' THEN 1 MONTH WHEN 'year' THEN 1 YEAR
      END
    )
  FROM dates_config
  -- 递归终止条件：当前周期起始日期不超过全局结束日期
  WHERE period_start < @global_end_date
)

-- 以下部分完全沿用你原来的查询逻辑，不需要做任何改动
SELECT
  dates_config.period_start AS start_date,
  dates_config.period_end AS end_date,
  id,
  COUNT(*)
FROM dates_config
INNER JOIN -- 保留你原来的表关联逻辑、关联条件
  -- 你的业务表
-- 建议保留时间范围过滤条件，减少全表扫描开销：
-- 业务表.时间字段 >= dates_config.period_start
-- AND 业务表.时间字段 < dates_config.period_end

• 切换统计粒度时，完全不需要修改查询主体逻辑，只需要把@period_type的取值替换成对应类型即可：按日统计填day、按自然周统计填week、按自然月统计填month、按自然年统计填year
• 所有日期范围统一采用左闭右开的边界格式，和你原来按日统计的范围逻辑完全兼容，不会出现零点时间点重复统计、漏算的问题
• 自然周计算逻辑固定以周一为周期起点，符合国内通用的统计规则，不会出现部分数据库默认以周日为周起始的统计偏差
• 若要生成的周期数超过默认的递归深度限制，只需打开cte_max_recursion_depth的配置调整上限，即可支持最长十几年的按日粒度统计需求

如果使用的是不支持递归CTE的老版本MySQL，不需要做版本升级，只需要借助MySQL自带的系统表生成连续行号，逐次偏移计算日期范围即可，核心参数和上面的实现完全一致：

-- -------------------------- 全局参数配置，和上面完全一致 --------------------------
SET @global_start_date = '2020-11-01';
SET @global_end_date = '2020-12-31';
SET @period_type = 'month';
-- ---------------------------------------------------------------------------

SELECT
  -- 逐行偏移计算每个周期的起止时间，逻辑和递归版本完全一致
  CASE @period_type
    WHEN 'day' THEN DATE_ADD(base_start, INTERVAL rn*1 DAY)
    WHEN 'week' THEN DATE_ADD(base_start, INTERVAL rn*7 DAY)
    WHEN 'month' THEN DATE_ADD(base_start, INTERVAL rn*1 MONTH)
    WHEN 'year' THEN DATE_ADD(base_start, INTERVAL rn*1 YEAR)
  END AS start_date,
  CASE @period_type
    WHEN 'day' THEN DATE_ADD(base_start, INTERVAL (rn+1)*1 DAY)
    WHEN 'week' THEN DATE_ADD(base_start, INTERVAL (rn+1)*7 DAY)
    WHEN 'month' THEN DATE_ADD(base_start, INTERVAL (rn+1)*1 MONTH)
    WHEN 'year' THEN DATE_ADD(base_start, INTERVAL (rn+1)*1 YEAR)
  END AS end_date
FROM (
  -- 借助系统表生成连续行号，从0开始偏移
  SELECT @rn := @rn + 1 AS rn
  FROM information_schema.COLUMNS, (SELECT @rn := -1) init
  -- 限制生成的行数不超过需要的总周期数
  WHERE @rn <= CEIL(
    DATEDIFF(
      CASE @period_type
        WHEN 'day' THEN @global_start_date
        WHEN 'week' THEN DATE_SUB(@global_start_date, INTERVAL WEEKDAY(@global_start_date) DAY)
        WHEN 'month' THEN DATE_FORMAT(@global_start_date, '%Y-%m-01')
        WHEN 'year' THEN DATE_FORMAT(@global_start_date, '%Y-01-01')
      END,
      @global_end_date
    ) / 
    CASE @period_type WHEN 'day' THEN 1 WHEN 'week' THEN 7 WHEN 'month' THEN 30 WHEN 'year' THEN 365 END
  )
) row_offset
CROSS JOIN (
  -- 计算初始的周期基准起始日期
  SELECT
    CASE @period_type
      WHEN 'day' THEN @global_start_date
      WHEN 'week' THEN DATE_SUB(@global_start_date, INTERVAL WEEKDAY(@global_start_date) DAY)
      WHEN 'month' THEN DATE_FORMAT(@global_start_date, '%Y-%m-01')
      WHEN 'year' THEN DATE_FORMAT(@global_start_date, '%Y-01-01')
    END AS base_start
) base_config
-- 后面直接接你原来的业务关联、统计逻辑即可

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