如何在递归CTE中使用ROWNUM及解决筛选指定行失效问题

一、ROWNUM不生效的根本原因 #

ROWNUM是Oracle数据库特有的伪列，赋值逻辑为：查询结果生成过程中，仅当行通过所有筛选条件后才会被分配连续递增的ROWNUM，起始值为1。
当你使用WHERE ROWNUM = 4作为筛选条件时，执行逻辑如下：

1. 递归CTE返回第一行（pre=16，parent=14），此时分配ROWNUM=1，不满足=4的条件，被过滤
2. 递归CTE返回第二行（pre=14，parent=13），因为上一行未通过筛选，ROWNUM不会递增，仍分配ROWNUM=1，依旧不满足条件
3. 后续所有行都会被分配ROWNUM=1，永远无法匹配=4的条件，因此最终返回空结果。
   这是ROWNUM的固有特性：仅ROWNUM < N（N>1）的筛选条件能正常生效，ROWNUM >= N或ROWNUM = N（N>1）都会返回空结果。

二、更优的实现方案 #

方案1：简化层级字段方案（兼容所有Oracle版本） #

你原方案中CTE关联条件里的a.lvl <=4是多余的，直接删除即可，仅保留外层的层级过滤，不需要两次判断：

with ancestor_seq (
    pre, parent, lvl
) as (
    select pre, parent, 1 lvl
    from form_data_stage
    where pre = 16
    union all
    select d.pre, d.parent, a.lvl + 1
    from ancestor_seq a 
    inner join form_data_stage d on d.pre = a.parent
)
select pre, parent from ancestor_seq where lvl = 4;

该方案逻辑清晰，兼容Oracle 10g、11g等旧版本，性能优异，递归过程没有额外开销。

方案2：OFFSET FETCH语法（Oracle 12c及以上版本） #

如果你的数据库版本为12c及以上，可以直接用分页语法取第4行，不需要额外新增层级字段，代码更简洁：

with ancestor_seq (
    pre, parent
) as (
    select pre, parent
    from form_data_stage
    where pre = 16
    union all
    select d.pre, d.parent
    from ancestor_seq a 
    inner join form_data_stage d on d.pre = a.parent
)
select * from ancestor_seq OFFSET 3 ROWS FETCH NEXT 1 ROW ONLY;

OFFSET 3 ROWS代表跳过前3行，FETCH NEXT 1 ROW ONLY代表取接下来的1行，正好对应你需要的第4行结果。

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