Oracle SQL生成连续月份数据遇丢月及无限循环问题求助

你的问题主要出在两个地方：日期解析格式错误导致月份计算偏差，以及**CONNECT BY缺少递归限制条件**引发无限循环。下面一步步帮你修正：

问题根源分析 #

1. 日期解析错误：你用to_date(myear,'mm-yyyy')解析'Jan 10'这类格式，但'mm'是数字月份（比如01），而你的数据是月份缩写（Jan），应该用'Mon RR'格式（Mon匹配英文月份缩写，RR自动处理两位年份）。
2. 无限循环：CONNECT BY子句没有指定只针对当前行生成月份序列，Oracle会无限制地跨行递归，导致无限循环。

修正后的查询代码 #

WITH formatted_data AS (
    -- 第一步：正确解析日期并获取下一个记录的月份
    SELECT 
        val,
        TO_DATE(m_year, 'Mon RR') AS start_month,
        -- 最后一条记录的结束时间设为当前系统日期的月初，也可改为SYSDATE
        LEAD(TO_DATE(m_year, 'Mon RR'), 1, TRUNC(SYSDATE, 'MM')) OVER (ORDER BY TO_DATE(m_year, 'Mon RR')) AS next_month
    FROM test
)
SELECT 
    TO_CHAR(ADD_MONTHS(start_month, level - 1), 'DD-Mon-RR') AS months,
    val
FROM formatted_data
-- 生成从start_month到next_month的所有连续月份
CONNECT BY LEVEL <= MONTHS_BETWEEN(next_month, start_month) + 1
-- 关键：添加PRIOR条件限制递归仅在当前行内进行，避免无限循环
AND PRIOR rowid = rowid
AND PRIOR SYS_GUID() IS NOT NULL
-- 按月份排序确保结果有序
ORDER BY months;

代码解释 #

• formatted_data CTE：先把m_year转换成正确的日期类型，用LEAD函数获取下一条记录的月份，最后一条记录则用当前系统日期的月初作为结束节点（如果需要显示到sysdate当天，把TRUNC(SYSDATE, 'MM')改成SYSDATE即可）。
• CONNECT BY子句：
  • LEVEL <= MONTHS_BETWEEN(next_month, start_month) + 1：计算需要生成的月份数量，确保从起始月到结束月的所有月份都被覆盖。
  • PRIOR rowid = rowid + PRIOR SYS_GUID() IS NOT NULL：强制递归仅在当前行内执行，彻底避免跨行递归导致的无限循环（SYS_GUID()是为了兼容极端情况下rowid重复的场景）。
• 排序：最后按月份排序，保证输出结果是按时间顺序排列的。

验证结果 #

这个查询会生成你期望的连续月份序列，每个月份继承对应时间段的val值，不会出现月份丢失或无限循环的问题。

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