Oracle SQL WITH子句生成日期区间临时表的精度调整问题

修改后的查询语句 #

WITH params AS (
    SELECT 
        TO_DATE(:P6_DATUM_VON, 'DD-MM-YYYY') AS start_date,
        TO_DATE(:P6_DATUM_BIS, 'DD-MM-YYYY') AS end_date
    FROM DUAL
),
monlist(mon, next_mon) AS (
    -- 处理起始日期到下一月初的区间
    SELECT 
        p.start_date,
        TRUNC(ADD_MONTHS(p.start_date, 1), 'MM')
    FROM params p
    WHERE ADD_MONTHS(p.start_date, 1) <= p.end_date
    
    UNION ALL
    
    -- 生成中间的完整月份区间
    SELECT 
        TRUNC(ADD_MONTHS(p.start_date, level), 'MM'),
        TRUNC(ADD_MONTHS(p.start_date, level + 1), 'MM')
    FROM params p
    CONNECT BY TRUNC(ADD_MONTHS(p.start_date, level + 1), 'MM') <= p.end_date
    
    UNION ALL
    
    -- 处理结束日期非月末的最后一段区间
    SELECT 
        TRUNC(p.end_date, 'MM'),
        p.end_date
    FROM params p
    WHERE 
        LAST_DAY(p.end_date) <> p.end_date
        AND TRUNC(p.end_date, 'MM') > TRUNC(p.start_date, 'MM')
    
    UNION ALL
    
    -- 处理起始和结束日期在同一个月的情况
    SELECT 
        p.start_date,
        p.end_date
    FROM params p
    WHERE TRUNC(p.start_date, 'MM') = TRUNC(p.end_date, 'MM')
)
SELECT 
    TO_CHAR(mon, 'DD-MM-YYYY') AS mon,
    TO_CHAR(next_mon, 'DD-MM-YYYY') AS next_mon
FROM monlist
ORDER BY mon;

关键修改说明 #

1. 新增params CTE：将输入的日期参数转换为日期类型并存储，避免重复转换，提升代码可读性。
2. 分场景生成区间：
   • 第一个区间直接使用原始起始日期，到下一月的月初，保留起始日的精准性。
   • 中间月份用TRUNC(..., 'MM')生成完整的月初到下月初区间，覆盖跨月的完整月份。
   • 最后一段区间判断结束日期是否为当月月末，若非月末则生成当月月初到结束日期的区间。
   • 单独处理起始和结束在同一个月的场景，直接返回起始到结束的完整区间。
3. 排序输出：按起始日期排序，保证区间顺序符合时间逻辑。

示例输出 #

当输入起始日期05-01-2022、结束日期15-10-2022时，输出结果如下：

mon          next_mon
05-01-2022   01-02-2022
01-02-2022   01-03-2022
01-03-2022   01-04-2022
...
01-09-2022   01-10-2022
01-10-2022   15-10-2022

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