生成含美国节假日及长周末标签的日期列表的SQL实现需求

我来帮你把这个SQL调整到位，完美覆盖你提到的所有需求：

需求回顾 #

咱们需要生成的日期列表要满足：

• 日期范围：从当前日期3年前的1月1日到未来2年的12月31日
• 标注规则：
  • 固定节日直接标记：圣诞节(XMAS)、新年前夜(NYE)、新年元旦(NYD)
  • 长周末节假日（标记节日及此前周六起的所有日期）：阵亡将士纪念日(MEM)、独立日(JUL4)、劳动节(LABOR)
  • 其余日期显示英文星期名称

完善后的SQL代码 #

WITH holiday_ranges AS (
    -- 计算每年阵亡将士纪念日（5月最后一个周一）的长周末：周六至周一
    SELECT 
        TRUNC(LAST_DAY(ADD_MONTHS(TRUNC(sysdate, 'YYYY'), (y-1)*12 + 4)), 'IW') - 5 AS start_date,
        TRUNC(LAST_DAY(ADD_MONTHS(TRUNC(sysdate, 'YYYY'), (y-1)*12 + 4)), 'IW') - 3 AS end_date,
        'MEM' AS holiday_label
    FROM (SELECT LEVEL y FROM DUAL CONNECT BY LEVEL <= 6) -- 覆盖3年前到2年后，共6个年份
    UNION ALL
    -- 计算每年独立日（7月4日）的长周末：此前周六至7月4日当天
    SELECT 
        TRUNC(TO_DATE(y || '-07-04', 'YYYY-MM-DD'), 'IW') - 5 AS start_date,
        TO_DATE(y || '-07-04', 'YYYY-MM-DD') AS end_date,
        'JUL4' AS holiday_label
    FROM (SELECT TO_CHAR(TRUNC(sysdate, 'YYYY') - INTERVAL '3' YEAR + INTERVAL (LEVEL-1) YEAR, 'YYYY') y FROM DUAL CONNECT BY LEVEL <= 6)
    UNION ALL
    -- 计算每年劳动节（9月第一个周一）的长周末：周六至周一
    SELECT 
        TRUNC(ADD_MONTHS(TRUNC(sysdate, 'YYYY'), (y-1)*12 + 8), 'IW') - 5 AS start_date,
        TRUNC(ADD_MONTHS(TRUNC(sysdate, 'YYYY'), (y-1)*12 + 8), 'IW') - 3 AS end_date,
        'LABOR' AS holiday_label
    FROM (SELECT LEVEL y FROM DUAL CONNECT BY LEVEL <= 6)
)
SELECT 
    s.staydate,
    CASE
        -- 优先匹配固定节日，避免被长周末覆盖
        WHEN TO_CHAR(s.staydate, 'MMDD') = '1225' THEN 'XMAS'
        WHEN TO_CHAR(s.staydate, 'MMDD') = '1231' THEN 'NYE'
        WHEN TO_CHAR(s.staydate, 'MMDD') = '0101' THEN 'NYD'
        -- 匹配长周末范围内的日期
        WHEN hr.holiday_label IS NOT NULL THEN hr.holiday_label
        -- 其他日期显示英文星期缩写
        ELSE TO_CHAR(s.staydate, 'dy', 'NLS_DATE_LANGUAGE=ENGLISH')
    END AS daylabel
FROM (
    -- 生成符合范围的所有日期：3年前元旦 → 2年后除夕
    SELECT 
        TRUNC(sysdate, 'YYYY') - INTERVAL '3' YEAR + (LEVEL - 1) AS staydate
    FROM DUAL
    CONNECT BY LEVEL <= (TRUNC(add_months(sysdate, 24), 'YYYY') + INTERVAL '1' YEAR - INTERVAL '1' DAY) - (TRUNC(sysdate, 'YYYY') - INTERVAL '3' YEAR) + 1
) s
LEFT JOIN holiday_ranges hr ON s.staydate BETWEEN hr.start_date AND hr.end_date
ORDER BY s.staydate;

关键修改点说明 #

1. 精准日期范围：

   • 用TRUNC(sysdate, 'YYYY') - INTERVAL '3' YEAR准确获取3年前的1月1日，避免原SQL中硬编码ROWNUM导致的日期范围不足问题
   • 用TRUNC(add_months(sysdate, 24), 'YYYY') + INTERVAL '1' YEAR - INTERVAL '1' DAY获取未来2年的12月31日，自动适配不同年份的天数

2. 长周末逻辑实现：

   • 阵亡将士纪念日：通过LAST_DAY找到5月最后一天，再用TRUNC(..., 'IW')定位该周的周一，往前推5天得到长周末的起始周六
   • 独立日：直接锁定每年7月4日，找到该日期所在周的周六作为长周末起点
   • 劳动节：定位9月的第一个周一，往前推5天得到长周末起始周六

3. 标注优先级：

   • 固定节日的判断放在最前面，确保像元旦这样可能落在长周末的日期，优先显示节日标记而非长周末标记
   • 星期显示指定NLS_DATE_LANGUAGE=ENGLISH，保证输出的星期缩写是英文，不受数据库语言设置影响

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