PL/SQL查询需求：提取日期列中排除周末的前2天数据

需求说明 #

需要从holddbo.pos表的eff_date列中，提取指定目标日期往前数的2个工作日数据，自动排除周六和周日，具体场景示例：

• 目标日期为2022-11-15时，返回2022-11-14、2022-11-11的数据
• 目标日期为2022-11-14时，返回2022-11-11、2022-11-10的数据
• 目标日期为2022-11-11时，返回2022-11-10、2022-11-09的数据

解决方案 #

方案1：使用CONNECT BY生成工作日列表 #

该方案通过生成连续日期并自动跳过周末，确保获取到准确的2个工作日：

SELECT *
FROM holddbo.pos
WHERE eff_date IN (
    SELECT TRUNC(:target_date) - LEVEL + 1 - 
           CASE 
               WHEN TO_CHAR(TRUNC(:target_date) - LEVEL + 1, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') IN ('SAT', 'SUN') THEN 
                   CASE TO_CHAR(TRUNC(:target_date) - LEVEL + 1, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH')
                       WHEN 'SAT' THEN 1
                       WHEN 'SUN' THEN 2
                   END
               ELSE 0
           END
    FROM dual
    CONNECT BY LEVEL <= 2 + 
               (SELECT COUNT(*)
                FROM dual
                WHERE TO_CHAR(TRUNC(:target_date) - 1, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') IN ('SAT', 'SUN')
                UNION ALL
                SELECT COUNT(*)
                FROM dual
                WHERE TO_CHAR(TRUNC(:target_date) - 2, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') IN ('SAT', 'SUN'))
    ORDER BY 1 DESC
    FETCH FIRST 2 ROWS ONLY
)

逻辑说明：

1. 用CONNECT BY生成目标日期前N天的序列，N=2加上前2天内的周末天数，避免因周末导致取不到足够的工作日
2. 通过CASE语句判断日期是否为周末，若是则自动往前偏移对应天数（周六偏移1天，周日偏移2天）
3. 最后排序并取前2个日期，确保结果是最新的2个工作日

方案2：递归CTE实现（更易读） #

用递归公共表达式（CTE）逐步收集工作日，直到凑够2个：

WITH workdays AS (
    -- 先取目标日期前1天，判断是否为工作日
    SELECT TRUNC(:target_date) - 1 AS day
    FROM dual
    WHERE TO_CHAR(TRUNC(:target_date) - 1, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') NOT IN ('SAT', 'SUN')
    UNION ALL
    -- 递归获取下一个工作日，跳过周末
    SELECT w.day - 1 - 
           CASE WHEN TO_CHAR(w.day - 1, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') IN ('SAT', 'SUN') THEN 
               CASE TO_CHAR(w.day - 1, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH')
                   WHEN 'SAT' THEN 1
                   WHEN 'SUN' THEN 2
               END
           ELSE 0
           END
    FROM workdays w
    WHERE (SELECT COUNT(*) FROM workdays) < 2
)
SELECT p.*
FROM holddbo.pos p
JOIN workdays w ON p.eff_date = w.day
ORDER BY w.day DESC;

注意事项 #

• 使用NLS_DATE_LANGUAGE=ENGLISH确保星期判断不受数据库语言环境影响，避免不同语言下星期标识不一致导致的错误
• 若eff_date包含时间部分，必须用TRUNC()函数截断时间，保证日期匹配准确

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