Oracle使用子查询获取递归层级触发ORA-01427错误求解

错误根因 #

你遇到的ORA-01427: single-row subquery returns more than one row报错原因如下：

• SELECT列表中的子查询属于标量子查询，语法要求必须仅返回1行1列的结果
• 你编写的CONNECT BY递归查询会返回整个递归树所有节点的level值，触发多行返回的报错
  除此之外你的原SQL还有两个隐藏问题：
• 外层aston a和buik b没有写关联条件，会生成笛卡尔积，返回大量无效重复行
• 子查询硬编码了START WITH b.identifier = 'B091000656'，所有行返回的层级都是固定起始点的层级，不符合按每行identifier匹配对应层级的需求

修复方案 #

方案1：递归逻辑提取为CTE关联查询（推荐，性能更好） #

提前计算所有identifier对应的层级，再和外层业务表关联：

WITH recursive_hier AS (
    SELECT 
        b.identifier,
        LEVEL AS hier_level
    FROM buik b
    INNER JOIN material m 
        ON b.auftrag = m.auftrag
    -- 若根节点固定为B091000656保留该行，否则根据业务调整起始条件
    START WITH b.identifier = 'B091000656'
    CONNECT BY PRIOR m.istcharge = b.identifier
)
SELECT 
    a.id, 
    b.identifier,
    r.hier_level
FROM aston a
-- 请补全aston和buik的实际关联条件，替换下方占位符
INNER JOIN buik b 
    ON a.buik_id = b.id 
INNER JOIN recursive_hier r 
    ON b.identifier = r.identifier

方案2：调整标量子查询保证单行返回 #

如果需要每行的identifier作为递归起始点计算层级，可通过关联外层值+聚合函数保证子查询单行返回：

SELECT 
    a.id, 
    b.identifier,
    (
        SELECT MAX(LEVEL)
        FROM buik b_inner, material m
        WHERE b_inner.auftrag = m.auftrag
        -- 关联外层当前行的identifier作为递归起始点
        START WITH b_inner.identifier = b.identifier
        CONNECT BY PRIOR m.istcharge = b_inner.identifier
    ) AS hier_level
FROM aston a
-- 请补全aston和buik的实际关联条件，替换下方占位符
INNER JOIN buik b 
    ON a.buik_id = b.id

注意事项 #

• 必须补全aston和buik的关联条件，避免笛卡尔积生成无效数据
• 若递归路径存在多个分支，可根据业务需求选择MAX(LEVEL)/MIN(LEVEL)/添加过滤条件，保证子查询仅返回1行结果

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