基于起止日期按周重复行的SQL查询实现需求

这个需求完全可行，核心思路是针对原表的每一条记录，单独生成其起止日期范围内匹配指定Weekday的所有日期，再将原记录与这些日期做关联展开，完全支持单日多条班次记录的场景。

关键步骤说明 #

1. 日期格式转换：原表的Startdate和Enddate是DD-MM-YYYY格式，需要先转换为数据库可识别的日期类型，避免日期计算错误。
2. 生成目标日期序列：对每条原记录，生成从Startdate到Enddate（含首尾）的所有日期，再筛选出与Weekday匹配的日期。
3. 关联展开记录：将原记录与筛选后的日期序列做关联，得到每条班次对应的具体运行日期。

具体SQL实现（以MySQL为例） #

WITH RECURSIVE date_range AS (
    -- 初始化：取每条记录的起始日期，同时处理可能的起止日期顺序颠倒的情况
    SELECT 
        Route,
        Season,
        Weekday,
        Direction,
        Startdate,
        Enddate,
        STR_TO_DATE(LEAST(Startdate, Enddate), '%d-%m-%Y') AS current_date,
        STR_TO_DATE(GREATEST(Startdate, Enddate), '%d-%m-%Y') AS end_date
    FROM bus_schedule
    UNION ALL
    -- 递归生成后续日期
    SELECT 
        Route,
        Season,
        Weekday,
        Direction,
        Startdate,
        Enddate,
        DATE_ADD(current_date, INTERVAL 1 DAY),
        end_date
    FROM date_range
    WHERE current_date < end_date
)
-- 筛选匹配Weekday的日期，并输出最终结果
SELECT 
    Route,
    Season,
    Weekday,
    Direction,
    Startdate,
    Enddate,
    DATE_FORMAT(current_date, '%d-%m-%Y') AS Date
FROM date_range
-- 匹配星期：注意MySQL的DAYNAME返回英文星期名，需与原表Weekday字段一致
WHERE DAYNAME(current_date) = Weekday
ORDER BY Route, Date;

其他数据库适配说明 #

• PostgreSQL：用generate_series替代递归CTE生成日期序列，星期匹配用TO_CHAR(current_date, 'FMDay')：

  SELECT 
      bs.Route,
      bs.Season,
      bs.Weekday,
      bs.Direction,
      bs.Startdate,
      bs.Enddate,
      TO_CHAR(d.date, 'DD-MM-YYYY') AS Date
  FROM bus_schedule bs
  CROSS JOIN LATERAL generate_series(
      TO_DATE(LEAST(bs.Startdate, bs.Enddate), 'DD-MM-YYYY'),
      TO_DATE(GREATEST(bs.Startdate, bs.Enddate), 'DD-MM-YYYY'),
      INTERVAL '1 day'
  ) d(date)
  WHERE TO_CHAR(d.date, 'FMDay') = bs.Weekday
  ORDER BY bs.Route, d.date;
  

• SQL Server：用递归CTE生成日期，星期匹配用DATENAME(WEEKDAY, current_date)。

补充说明 #

• 原表的repeat字段可以通过生成的记录数自动计算（比如用COUNT(*) OVER (PARTITION BY Route, Season, Weekday, Direction, Startdate, Enddate)），无需依赖原表的该字段，避免数据不一致。
• 若原表中存在Startdate > Enddate的情况（如示例中第二行的笔误），用LEAST和GREATEST可以自动修正日期范围顺序。

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