获取最终管理器oracle

xzabzqsa 于 2022-11-22 发布在 Oracle

关注(0)|答案(1)|浏览(102)

我有以下格式的数据。

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'

有谁能纠正我遗漏的小东西，以获得正确的输出吗？TIA

oracle

来源：https://stackoverflow.com/questions/74355403/get-ultimate-manager-oracle

1条答案

按热度按时间

xlpyo6sf1#

我认为您的查询很好-您只需在执行时选择根元素即可

SQL> with t as
  2  ( select 1 emp, 'A' floc, 'B' tloc, 'Road' v from dual union all
  3    select 1 emp,'B' floc, 'C' tloc, 'Ship' v from dual union all
  4    select 1 emp,'C' floc, 'D' tloc, 'Air' v from dual union all
  5    select 1 emp,'X' floc, 'D' tloc, 'Bus' v from dual
  6  )
  7  select t.*, level
  8  from T
  9  where emp=1
 10  CONNECT BY nocycle  floc= PRIOR tloc and prior emp=emp
 11  START WITH floc ='A';

       EMP F T V         LEVEL
---------- - - ---- ----------
         1 A B Road          1
         1 B C Ship          2
         1 C D Air           3

以便再让出

SQL> with t as
  2  ( select 1 emp, 'A' floc, 'B' tloc, 'Road' v from dual union all
  3    select 1 emp,'B' floc, 'C' tloc, 'Ship' v from dual union all
  4    select 1 emp,'C' floc, 'D' tloc, 'Air' v from dual union all
  5    select 1 emp,'X' floc, 'D' tloc, 'Bus' v from dual
  6  )
  7  select emp,
  8         connect_by_root floc  from_loc,
  9         tloc to_location,
 10         ltrim(sys_connect_by_path(v,'-'),'-') path
 11  from T
 12  where emp=1 and CONNECT_BY_ISLEAF=1
 13  CONNECT BY nocycle   floc= PRIOR tloc and prior emp=emp
 14  START WITH floc ='A';

       EMP F T PATH
---------- - - ------------------------------
         1 A D Road-Ship-Air

赞(0）回复(0）举报 2022-11-22

我来回答

获取最终管理器oracle

1条答案

相关问题

热门标签

最新问答