如何在Oracle SQL中仅输出两日期区间内的指定星期日期？

现有代码说明 #

• 用于生成指定星期几（如仅周一、周一至周五等）的代码：

school_days AS
  (
     SELECT to_char(trunc(sysdate ,'D') + LEVEL - sw.LEV_1, 'dy') as school_day
       FROM SCHOOL_WEEKS sw
    CONNECT BY LEVEL <= sw.LEV_2
  ),

• 用于生成起始日期到结束日期之间所有日期的代码：

all_dates AS 
( 
    SELECT sw.sc_start_date + LEVEL-1 as weekday_date, to_char((sw.sc_start_date + LEVEL-1), 'DAY') as weekday_day
      FROM SCHOOL_WEEKS sw
   CONNECT BY sw.sc_start_date + LEVEL-1 <=  sw.sc_end_date
)

完善后的查询方案 #

要筛选出两个日期之间的指定星期日期，可通过关联两个CTE或直接引用子查询实现，以下提供两种可行写法：

写法1：IN子查询筛选

将school_days作为子查询放入IN条件中，注意统一星期格式的大小写（'dy'为小写缩写，'DAY'为全大写，需两边匹配一致）：

WITH school_days AS
  (
     SELECT to_char(trunc(sysdate ,'D') + LEVEL - sw.LEV_1, 'dy') as school_day
       FROM SCHOOL_WEEKS sw
    CONNECT BY LEVEL <= sw.LEV_2
  ),
all_dates AS 
( 
    SELECT sw.sc_start_date + LEVEL-1 as weekday_date, to_char((sw.sc_start_date + LEVEL-1), 'dy') as weekday_day
      FROM SCHOOL_WEEKS sw
   CONNECT BY sw.sc_start_date + LEVEL-1 <=  sw.sc_end_date
)
SELECT * 
FROM all_dates
WHERE weekday_day IN (SELECT school_day FROM school_days);

写法2：JOIN关联筛选

通过星期缩写字段关联两个CTE，逻辑更直观：

WITH school_days AS
  (
     SELECT to_char(trunc(sysdate ,'D') + LEVEL - sw.LEV_1, 'dy') as school_day
       FROM SCHOOL_WEEKS sw
    CONNECT BY LEVEL <= sw.LEV_2
  ),
all_dates AS 
( 
    SELECT sw.sc_start_date + LEVEL-1 as weekday_date, to_char((sw.sc_start_date + LEVEL-1), 'dy') as weekday_day
      FROM SCHOOL_WEEKS sw
   CONNECT BY sw.sc_start_date + LEVEL-1 <=  sw.sc_end_date
)
SELECT ad.*
FROM all_dates ad
JOIN school_days sd ON ad.weekday_day = sd.school_day;

注意事项 #

• 务必保证to_char的格式符一致：若school_days用'dy'（如mon），all_dates也要对应使用'dy'，避免因大小写不匹配导致筛选失效；若使用全大写的'DAY'，则两边统一该格式。
• 若SCHOOL_WEEKS表存在多条数据，建议在CONNECT BY中添加PRIOR sw.主键字段 = sw.主键字段 AND PRIOR SYS_GUID() IS NOT NULL（替换为表实际主键），防止生成笛卡尔积。

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