主页 / dba / 问题 / 207267
Accepted
user151210
Asked: 2018-05-20 02:45:41 +0800 CST

我想要如下输出

  • 772
PARENT_COIL  CHILD_COIL      ROUTE ORDER_NO ORDER_NO

AB1              AB2                   1     111
AB1              AB2                   1     111
BB1              BB2                   1     222
AB3              AB4                   3     111
BB2              BB3                   2     222
AB2              AB3                   2     111
BB3              BB4                   3     222

输出应该是

FG      PARENT_COIL      ROUTE

AB4      AB1               1~2~3
BB4      BB1               1~2~3

我们有表 1，其中Parent_coilAB1 经过路线 1 并成为child_coilAB2，类似地子线圈 AB2（用于 AB3 的父线圈）经过路线 2 并成为 AB3。成品是 AB4。现在我需要一个输出表，它显示成品和它起源的母线圈以及它经过的路线。

oracle oracle-11g-r2

1 个回答

  1. Best Answer

    数据：

    create table data (parent_coil char(3 char), child_coil char(3 char), route_order_no number, order_no number);
insert into data values ('AB1', 'AB2', 1, 111);
insert into data values ('AB1', 'AB2', 1, 111);
insert into data values ('BB1', 'BB2', 1, 222);
insert into data values ('AB3', 'AB4', 3, 111);
insert into data values ('BB2', 'BB3', 2, 222);
insert into data values ('AB2', 'AB3', 2, 111);
insert into data values ('BB3', 'BB4', 3, 222);
commit;

    基本分层查询：

    select child_coil, parent_coil
from
  data
connect by prior child_coil = parent_coil
start with route_order_no = 1;

CHI PAR
--- ---
AB2 AB1
AB3 AB2
AB4 AB3
AB2 AB1
AB3 AB2
AB4 AB3
BB2 BB1
BB3 BB2
BB4 BB3

    用根替换父级：

    select child_coil, connect_by_root parent_coil as parent_coil
from
  data
connect by prior child_coil = parent_coil
start with route_order_no = 1;

CHI PAR
--- ---
AB2 AB1
AB3 AB1
AB4 AB1
AB2 AB1
AB3 AB1
AB4 AB1
BB2 BB1
BB3 BB1
BB4 BB1

    添加完整路线：

    select child_coil, connect_by_root parent_coil as parent_coil, substr(sys_connect_by_path(route_order_no, '~'), 2) as route
from
  data
connect by prior child_coil = parent_coil
start with route_order_no = 1;

CHI PAR ROUTE
--- --- -----
AB2 AB1 1    
AB3 AB1 1~2  
AB4 AB1 1~2~3
AB2 AB1 1    
AB3 AB1 1~2  
AB4 AB1 1~2~3
BB2 BB1 1    
BB3 BB1 1~2  
BB4 BB1 1~2~3

    查找“完整”路线CONNECT_BY_ISLEAF：

    select
  child_coil,
  connect_by_root parent_coil as parent_coil,
  substr(sys_connect_by_path(route_order_no, '~'), 2) as route,
  connect_by_isleaf isleaf
from
  data
connect by prior child_coil = parent_coil
start with route_order_no = 1;

CHI PAR ROUTE     ISLEAF
--- --- ----- ----------
AB2 AB1 1              0
AB3 AB1 1~2            0
AB4 AB1 1~2~3          1
AB2 AB1 1              0
AB3 AB1 1~2            0
AB4 AB1 1~2~3          1
BB2 BB1 1              0
BB3 BB1 1~2            0
BB4 BB1 1~2~3          1

    最终查询：

    select distinct fg, parent_coil, route from (
select
  child_coil as fg,
  connect_by_root parent_coil as parent_coil,
  substr(sys_connect_by_path(route_order_no, '~'), 2) as route,
  connect_by_isleaf isleaf
from
  data
connect by prior child_coil = parent_coil
start with route_order_no = 1
)
where isleaf = 1 order by fg;

FG  PAR ROUTE
--- --- -----
AB4 AB1 1~2~3
BB4 BB1 1~2~3
    • 3

相关问题