Oracle SQL查询两个日期间指定工作日对应日期的实现方案

编写SQL查询语句，基于CALENDAR表的日历配置，返回配置中START_DATE与END_DATE日期区间内，匹配对应WEEKDAY（星期几）的所有日期，结果按SEQNUM字段指定的顺序输出。

示例场景 #

编号为99的日历配置起止日期范围为2020-07-29至2021-08-07，规则如下：

• seqnum=1 对应 WEDNESDAY（周三）
• seqnum=2 对应 SATURDAY（周六）
  需返回该区间内所有周三、周六的对应日期。

需求概要：根据CALENDAR表的配置规则，查询指定日期范围内、匹配配置中指定星期值的所有日期，结果按SEQNUM定义的顺序输出。

示例日历表结构截图

CREATE TABLE CALENDAR
(
  CALENDAR_NAME  VARCHAR2(500 CHAR),
  START_DATE     VARCHAR2(100 CHAR),
  END_DATE       VARCHAR2(100 CHAR),
  SEQNUM         NUMBER,
  WEEKDAY        VARCHAR2(9 CHAR),
  STARTTIME      VARCHAR2(8 CHAR)
);

Insert into CALENDAR
   (CALENDAR_NAME, START_DATE, END_DATE, SEQNUM, WEEKDAY, 
    STARTTIME)
 Values
   ('99', '2020-07-29', '2021-08-07', 1, 'WEDNESDAY', 
    '17:00:00');
Insert into CALENDAR
   (CALENDAR_NAME, START_DATE, END_DATE, SEQNUM, WEEKDAY, 
    STARTTIME)
 Values
   ('99', '2020-07-29', '2021-08-07', 2, 'SATURDAY', 
    '17:00:00');
Insert into CALENDAR
   (CALENDAR_NAME, START_DATE, END_DATE, SEQNUM, WEEKDAY, 
    STARTTIME)
 Values
   ('179', '2000-01-02', '2021-02-01', 1, 'MONDAY', 
    '18:00:00');
-- 原测试数据此处笔误写为CALENDARR，已修正为CALENDAR
Insert into CALENDAR
   (CALENDAR_NAME, START_DATE, END_DATE, SEQNUM, WEEKDAY, 
    STARTTIME)
 Values
   ('179', '2000-01-02', '2021-02-01', 2, 'WEDNESDAY', 
    '18:00:00');
Insert into CALENDAR
   (CALENDAR_NAME, START_DATE, END_DATE, SEQNUM, WEEKDAY, 
    STARTTIME)
 Values
   ('179', '2000-01-02', '2021-02-01', 3, 'FRIDAY', 
    '18:00:00');
Insert into CALENDAR
   (CALENDAR_NAME, START_DATE, END_DATE, SEQNUM, WEEKDAY, 
    STARTTIME)
 Values
   ('179', '2000-01-02', '2021-02-01', 4, 'SUNDAY', 
    '18:00:00');
Insert into CALENDAR
   (CALENDAR_NAME, START_DATE, END_DATE, SEQNUM, WEEKDAY, 
    STARTTIME)
 Values
   ('772', '2000-01-02', '2021-02-01', 1, 'TUESDAY', 
    '18:00:00');
Insert into CALENDAR
   (CALENDAR_NAME, START_DATE, END_DATE, SEQNUM, WEEKDAY, 
    STARTTIME)
 Values
   ('772', '2000-01-02', '2021-02-01', 2, 'WEDNESDAY', 
    '18:00:00');
Insert into CALENDAR
   (CALENDAR_NAME, START_DATE, END_DATE, SEQNUM, WEEKDAY, 
    STARTTIME)
 Values
   ('772', '2000-01-02', '2021-02-01', 3, 'THURSDAY', 
    '18:00:00');
Insert into CALENDAR
   (CALENDAR_NAME, START_DATE, END_DATE, SEQNUM, WEEKDAY, 
    STARTTIME)
 Values
   ('772', '2000-01-02', '2021-02-01', 4, 'FRIDAY', 
    '18:00:00');
Insert into CALENDAR
   (CALENDAR_NAME, START_DATE, END_DATE, SEQNUM, WEEKDAY, 
    STARTTIME)
 Values
   ('772', '2000-01-02', '2021-02-01', 5, 'SATURDAY', 
    '18:00:00');
Insert into CALENDAR
   (CALENDAR_NAME, START_DATE, END_DATE, SEQNUM, WEEKDAY, 
    STARTTIME)
 Values
   ('772', '2000-01-02', '2021-02-01', 6, 'SUNDAY', 
    '18:00:00');
Insert into CALENDAR
   (CALENDAR_NAME, START_DATE, END_DATE, SEQNUM, WEEKDAY, 
    STARTTIME)
 Values
   ('772', '2000-01-02', '2021-02-01', 7, 'MONDAY', 
    '18:00:00');
COMMIT;

核心逻辑分两步：

1. 生成每个日历配置对应起止日期范围内的所有连续日期
2. 筛选出日期星期值与配置中WEEKDAY匹配的记录，按SEQNUM、日期排序输出

递归CTE实现（适合10年以内跨度的日期范围） #

WITH date_range AS (
    SELECT 
        CALENDAR_NAME,
        TO_DATE(START_DATE, 'YYYY-MM-DD') AS curr_date,
        TO_DATE(END_DATE, 'YYYY-MM-DD') AS end_date,
        SEQNUM,
        WEEKDAY,
        STARTTIME
    FROM CALENDAR
    UNION ALL
    SELECT 
        CALENDAR_NAME,
        curr_date + 1 AS curr_date,
        end_date,
        SEQNUM,
        WEEKDAY,
        STARTTIME
    FROM date_range
    WHERE curr_date < end_date
)
SELECT 
    CALENDAR_NAME,
    TO_CHAR(curr_date, 'YYYY-MM-DD') AS MATCH_DATE,
    SEQNUM,
    WEEKDAY,
    STARTTIME
FROM date_range
-- Oracle中TO_CHAR(date, 'DAY')返回带空格填充的星期全称，截断后匹配
WHERE TRIM(TO_CHAR(curr_date, 'DAY')) = WEEKDAY
ORDER BY CALENDAR_NAME, SEQNUM, curr_date;

注意事项 #

• 表中START_DATE、END_DATE字段为字符串类型，查询时需用TO_DATE指定格式转换为日期类型，避免隐式转换报错
• 若日期范围跨度超过10年，递归CTE会触发Oracle层级超限错误，可改用CONNECT BY方式生成日期序列：

SELECT 
    c.CALENDAR_NAME,
    TO_CHAR(TO_DATE(c.START_DATE,'YYYY-MM-DD') + t.lv - 1, 'YYYY-MM-DD') AS MATCH_DATE,
    c.SEQNUM,
    c.WEEKDAY,
    c.STARTTIME
FROM CALENDAR c
JOIN (
    SELECT LEVEL lv 
    FROM (SELECT MAX(TO_DATE(END_DATE,'YYYY-MM-DD') - TO_DATE(START_DATE,'YYYY-MM-DD') +1) max_gap FROM CALENDAR)
    CONNECT BY LEVEL <= max_gap
) t
ON TO_DATE(c.START_DATE,'YYYY-MM-DD') + t.lv -1 <= TO_DATE(c.END_DATE,'YYYY-MM-DD')
WHERE TRIM(TO_CHAR(TO_DATE(c.START_DATE,'YYYY-MM-DD') + t.lv -1, 'DAY')) = c.WEEKDAY
ORDER BY c.CALENDAR_NAME, c.SEQNUM, TO_DATE(c.START_DATE,'YYYY-MM-DD') + t.lv -1;

99号日历部分返回结果示例 #

内容的提问来源于stack exchange，提问作者B-Rad