临时表分层查询结果正确但排序异常，CASE语句引发问题

问题现状 #

• 现有分层查询返回数据正确，但父子层级排序混乱，父级记录未紧随其子级显示
• 添加order siblings by b.id后未生效，反而出现父级记录被夹在子级中间/下方的情况
• 定位到核心诱因：新增CASE字段后排序彻底混乱，移除该字段后排序恢复正常

问题分析 #

1. 多余的DISTINCT干扰层级顺序：你的CTE中多次使用DISTINCT，如果my_table里不存在重复的(ID, LABEL, parent_id)记录，这步操作完全多余，还会让Oracle对结果集重新排序，破坏分层遍历的原生顺序。
2. 冗余的CTE结构：temp2通过自连接生成p_id，其实直接用原表的parent_id就能判断父节点，额外的JOIN+DISTINCT进一步打乱了层级关联的逻辑。
3. CASE字段的排序权重冲突：当新增CASE字段后，若未将其纳入order siblings by的排序规则，Oracle的分层排序逻辑会被干扰，导致层级顺序错乱。

修复方案 #

方案1：简化查询结构，移除多余操作（优先推荐）

去掉不必要的DISTINCT和冗余CTE，直接基于原表字段构建分层查询，确保层级顺序不被干扰：

with temp1 as (
select 
    b.ID,
    b.LABEL,
    b.parent_id
from my_table b
where b.PROG_MODIF_ID=:P225_PROG_MODIF
)
select 
    b.ID,
    b.parent_id as p_id,
    b.LABEL,
    b.parent_id
from temp1 b
start with b.parent_id is null
connect by prior b.id = b.parent_id
order siblings by b.id;

方案2：保留CASE字段时的正确写法

如果必须保留CASE字段，需将其纳入order siblings by的排序条件，同时避免前置DISTINCT破坏层级：

with temp1 as (
select 
    b.ID,
    b.LABEL,
    b.parent_id,
    -- 替换成你的实际CASE逻辑
    CASE WHEN b.some_column = 'xxx' THEN 'A' ELSE 'B' END AS custom_field
from my_table b
where b.PROG_MODIF_ID=:P225_PROG_MODIF
)
select 
    b.ID,
    b.parent_id as p_id,
    b.LABEL,
    b.parent_id,
    b.custom_field
from temp1 b
start with b.parent_id is null
connect by prior b.id = b.parent_id
-- 按自定义字段+ID排序，确保层级内顺序符合预期
order siblings by b.custom_field, b.id;

方案3：必须使用DISTINCT时的处理方式

如果业务上确实需要去重，要确保DISTINCT在分层查询之后执行，避免干扰层级遍历：

with temp1 as (
select 
    b.ID,
    b.LABEL,
    b.parent_id,
    CASE WHEN ... THEN ... ELSE ... END AS custom_field
from my_table b
where b.PROG_MODIF_ID=:P225_PROG_MODIF
)
select distinct
    b.ID,
    b.parent_id as p_id,
    b.LABEL,
    b.parent_id,
    b.custom_field
from (
    select *
    from temp1 b
    start with b.parent_id is null
    connect by prior b.id = b.parent_id
    order siblings by b.id
) b;

关键注意点 #

• 分层查询中，order siblings by是专门用于控制同一父节点下子级排序的关键字，必须直接作用在CONNECT BY的结果集上，不能被前置的DISTINCT、GROUP BY等操作打乱。
• 尽量减少分层查询前置步骤中的数据变换操作，保持层级关联的原生逻辑，是确保排序正确的核心。

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