Oracle层级查询（Connect by/Start with）结果修正求助

原始数据 #

Emp    to_location    from_location  Vehicle
---------------------------------------------
1      A                B             Road
1      B                C             Ship
1      C                D             Air
1      X                D             Bus

期望输出 #

Emp    ToL    FromL  Vehicle
--------------------------
1      A       D   Air

当前错误输出 #

使用CONNECT BY和START WITH语句后得到错误结果：

Emp   FromL    ToL  Path
--------------------------
1      B       C   Air

目标正确输出 #

实际需要的正确结果：

Emp   FromL    ToL  Path
--------------------------
1      D       A    Air

现有查询语句 #

with t as
( select 1 emp, 'A' tloc, 'B' floc, 'Road' v from dual union all
  select 1 emp,'B' tloc, 'C' floc, 'Ship' v from dual union all
  select 1 emp,'C' tloc, 'D' floc, 'Air' v from dual union all
  select 1 emp,'X' tloc, 'D' floc, 'Bus' v from dual
)
select emp,
       connect_by_root floc  from_loc,
       tloc to_location,
       v,
       CONNECT_BY_ISLEAF ch
from T
where CONNECT_BY_ISLEAF=1
CONNECT BY nocycle  prior floc= tloc and prior emp=emp
AND PRIOR SYS_GUID() IS NOT NULL
START WITH tloc ='A'

修正后的查询语句及解释 #

你的问题出在递归关联逻辑和起始点设置上，以下是修正后的查询及关键调整点：

匹配目标输出的查询 #

with t as
( select 1 emp, 'A' tloc, 'B' floc, 'Road' v from dual union all
  select 1 emp,'B' tloc, 'C' floc, 'Ship' v from dual union all
  select 1 emp,'C' tloc, 'D' floc, 'Air' v from dual union all
  select 1 emp,'X' tloc, 'D' floc, 'Bus' v from dual
)
select emp,
       connect_by_root tloc as ToL,
       floc as FromL,
       v as Vehicle
from T
where CONNECT_BY_ISLEAF = 1
CONNECT BY nocycle prior tloc = floc and prior emp = emp
START WITH floc = 'D'

关键修正点 #

1. 递归方向调整：将prior floc = tloc改为prior tloc = floc，让递归沿着D←C←B←A的反向链路遍历，确保能定位到完整路径的两个端点。
2. 起始点修改：START WITH从tloc='A'改为floc='D'，从最终的起点D出发回溯，找到链路的初始终点A。
3. 字段映射修正：
   • connect_by_root tloc获取根节点（D对应的链路起点）的tloc值，即A，作为ToL字段。
   • 叶子节点的floc值为D，作为FromL字段。
4. 保留叶子节点过滤：CONNECT_BY_ISLEAF=1确保只返回链路的端点记录，避免中间节点干扰。

执行上述查询后，将得到你需要的正确输出：

Emp    ToL    FromL  Vehicle
--------------------------
1      A       D   Air

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