Oracle中按日期范围拆分请假表日期列并生成自增记录的方法

当然有办法实现这个需求！在Oracle中，我们可以通过递归CTE或者CONNECT BY层级查询来将每条请假记录的日期范围拆分为单独的每日行，同时生成符合你要求的连续LEAVE_DTL_ID。以下是两种可行的方案：

方法1：使用递归CTE（WITH子句） #

递归CTE是比较直观的实现方式，先从原表获取初始记录，再递归生成后续的日期行：

WITH recursive_leave AS (
    -- 初始行：获取每条记录的起始日期
    SELECT
        LEAVE_DTL_ID,
        STAFF_ID,
        START_DT AS DATES,
        END_DT
    FROM leave
    UNION ALL
    -- 递归生成后续日期行，LEAVE_DTL_ID同步递增
    SELECT
        LEAVE_DTL_ID + 1,
        STAFF_ID,
        DATES + 1,
        END_DT
    FROM recursive_leave
    WHERE DATES + 1 <= END_DT -- 终止条件：日期达到结束日
)
SELECT
    LEAVE_DTL_ID,
    STAFF_ID,
    DATES
FROM recursive_leave
ORDER BY LEAVE_DTL_ID;

关键点说明： #

• 初始查询抓取每条记录的START_DT作为第一个DATES值；
• 递归部分每次将日期加1，同时LEAVE_DTL_ID也加1，直到日期等于END_DT；
• 最终结果按LEAVE_DTL_ID排序，确保顺序完全匹配你的预期。

方法2：使用CONNECT BY层级查询 #

这种方式利用Oracle的层级查询特性，通过LEVEL伪列快速生成日期序列：

SELECT
    l.LEAVE_DTL_ID + (LEVEL - 1) AS LEAVE_DTL_ID,
    l.STAFF_ID,
    l.START_DT + (LEVEL - 1) AS DATES
FROM leave l
CONNECT BY 
    LEVEL <= (l.END_DT - l.START_DT + 1) -- 计算当前记录的日期跨度天数
    AND PRIOR l.LEAVE_DTL_ID = l.LEAVE_DTL_ID -- 确保每条记录独立递归
    AND PRIOR SYS_GUID() IS NOT NULL -- 避免多记录时出现笛卡尔积
ORDER BY LEAVE_DTL_ID;

关键点说明： #

• LEVEL伪列代表当前层级，从1开始递增；
• LEVEL <= (END_DT - START_DT + 1)：比如你的示例中5天的范围，LEVEL会自动生成1到5的序列；
• START_DT + (LEVEL - 1)：生成从起始日到结束日的每一天；
• LEAVE_DTL_ID + (LEVEL - 1)：生成连续的LEAVE_DTL_ID，和你给出的示例格式完全一致；
• PRIOR SYS_GUID() IS NOT NULL：这是一个实用小技巧，防止多条记录之间产生不必要的关联，保证每个记录的递归过程独立。

额外注意事项 #

• 确保START_DT和END_DT是Oracle的DATE类型，如果是字符串存储，需要先用TO_DATE()转换，比如TO_DATE(START_DT, 'DD-MON-RR')；
• 如果需要全局唯一且完全连续的LEAVE_DTL_ID（不依赖原记录的ID），可以用ROW_NUMBER()函数替代原ID的递增逻辑，示例如下：

  SELECT
      ROW_NUMBER() OVER (ORDER BY l.LEAVE_DTL_ID, l.START_DT + (LEVEL - 1)) AS LEAVE_DTL_ID,
      l.STAFF_ID,
      l.START_DT + (LEVEL - 1) AS DATES
  FROM leave l
  CONNECT BY 
      LEVEL <= (l.END_DT - l.START_DT + 1)
      AND PRIOR l.LEAVE_DTL_ID = l.LEAVE_DTL_ID
      AND PRIOR SYS_GUID() IS NOT NULL
  ORDER BY LEAVE_DTL_ID;
  

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