Oracle中的分层SQL

Question

我有一个很大的表，里面有很多列，所以为了简单起见，我只选择了相关的列。

有组件和模块。组件可以用作最终产品，但可以构建在另一个组件=模块中。模也是如此。一个模可以作为最终产品，也可以构建在另一个组件=模中。

在实表中只有数字，但为了更好地理解，我使用关系分量modul因此例如componentA+componentB＝modulAB，但是componentA也可以用作最终项。在表的前面有两列，可以识别组件是否被用作工厂中的最终项目assembly_id-表示项是一个模块，因此必须有一些组件(子项(in_assembly_id-表示该项目将构建在另一个项目/模块中

如上所述，在两列都为-1的情况下，项可以是最终项，但同时该项可以构建在另一项(modul(中，因此存在另一行，其中列in_assembly_id<gt-1.在我的示例组件中

对于已经是带有某些组件的模块的项目也是如此，这些项目可以是最终项目，也可以构建在另一个项目(模块(中，指示在_assembly_id列中。

我的目标是为特定的项目找到模块项目，不管该项目是否也可以作为最终产品

我可以使用join，但只能使用一个级别。

select distinct c.item, c.in_assembly_id, modul, modul_id from items c ,lateral (select a.item modul,a.assembly_id modul_id from items a where a.assembly_id=c.in_assembly_id)
where c.item = 'componentA' and c.in_assembly_id <> -1;
ITEM        IN_ASSEMBLY_ID  MODUL   MODUL_ID
componentA  100             modulAC 100
componentA  50              modulAB 50

因此，要查看整个结构，因为modulAC是在modulACDE中构建的，需要分层查询才能看到这样的输出1.

ITEM        IN_ASSEMBLY_ID assembly_id type/level
componentA  -1             50          comp
modulAB     50             -1          modul
componentA  -1             100         comp
modulAC     100            -1          modul
modulAC     100            500         modul
modulACDE   500            -1          modul

最高模数

ITEM        IN_ASSEMBLY_ID assembly_id type/level
componentA  -1             50          comp
modulAB     50             -1          modul
componentA  -1             100         comp
modulACDE   500            -1          modul

我从connect-by子句开始，但它对我不起作用，我不知道为什么我会得到modulDE

select distinct * from items
connect by nocycle in_assembly_id = prior assembly_id
start with item = 'componentA' and in_assembly_id <> '-1';

这是我的数据

create table items (
item_id number,
item varchar2(12),
assembly_id number,
in_assembly_id number
);
insert into items values ( 1 , 'componentA' , -1 , -1 );
insert into items values ( 2 , 'componentA' , -1 , 50 );
insert into items values ( 3 , 'componentB' , -1 , 50 );
insert into items values ( 4 , 'modulAB' , 50 , -1 );
insert into items values ( 5 , 'componentA' , -1 , 100 );
insert into items values ( 6 , 'componentC' , -1 , 100);
insert into items values ( 7 , 'modulAC' , 100 , -1 );
insert into items values ( 7 , 'modulAC' , 100 , 500 );
insert into items values ( 8 , 'componentD' , -1 , 200 );
insert into items values ( 9 , 'componentE' , -1 , 200 );
insert into items values ( 10 , 'modulDE' , 200 , 500 );
insert into items values ( 11 , 'modulACDE' , 500 , -1 );
insert into items values ( 12 , 'componentF' , -1 , -1 );
insert into items values ( 13 , 'componentG' , -1 , -1 );

Answer 1

我的目标是为特定项目找到modul项目，不管该项目是否也可以作为最终产品

这将获得componentA:的所有modul

SELECT CONNECT_BY_ROOT( item_id ) AS root_item_id,
CONNECT_BY_ROOT( item ) AS root_item,
item_id,
item,
assembly_id,
SYS_CONNECT_BY_PATH( item_id, ',' ) AS path_item_id,
SYS_CONNECT_BY_PATH( item, ',' ) AS path_item
FROM   items
WHERE  assembly_id <> -1
START WITH
item = 'componentA'
AND    ( assembly_id, in_assembly_id ) NOT IN ((-1,-1))
CONNECT BY NOCYCLE
PRIOR in_assembly_id = assembly_id
ORDER SIBLINGS BY item, item_id;

哪个输出：

ROOT_ITEM_ID|ROOT_ITEM | ITEM_ID|ITEM|ASSEMBLY_ID|PATH_ITEM_ID| PATH_ITEM-----------：|：--------|-------：|：---------|---------:|：---------|：-------------------------------------2|componentA|4|modulAB|50|，2,4|，componentA，modulAB2|componentA|7|modulAC|100|，2,4,5,7|，componentA，modulAB，componentA、modulAC2|componentA|7|modulAC|100|，2,4,6,7|，componentA，modulAB，componentC，modulAC2|componentA|10|modulDE|200|，2,4,8,10|，componentA，modulAB，componentD，modulDE2|componentA|10|modulDE|200|，2,4,9,10|，componentA，modulAB，componentE，modulDE5|componentA|7|modulAC|100|，5,7|，componentA，modulAC5|componentA|10|modulDE|200|，5,7,8,10|，componentA，modulAC，componentD，modulDE5|componentA|10|modulDE|200|，5,7,9,10|，componentA，modulAC，componentE，modulDE5|componentA|7|modulAC|100|，5,7|，componentA，modulAC5|componentA|11|modulACDE|500|，5,7,11|，componentA，modulAC，modulACDE

db<gt；小提琴这里

---

更新

SELECT CONNECT_BY_ROOT( item_id ) AS root_item_id,
CONNECT_BY_ROOT( item ) AS root_item,
item_id,
item,
assembly_id,
SYS_CONNECT_BY_PATH( item_id, ',' ) AS path_item_id,
SYS_CONNECT_BY_PATH( item, ',' ) AS path_item
FROM   items
WHERE  assembly_id > -1
START WITH
item = 'componentA'
AND    ( assembly_id, in_assembly_id ) NOT IN ((-1,-1))
CONNECT BY NOCYCLE
PRIOR in_assembly_id = assembly_id
AND    assembly_id > -1
ORDER SIBLINGS BY item, item_id;

输出：

ROOT_ITEM_ID|ROOT_ITEM | ITEM_ID|ITEM|ASSEMBLY_ID|PATH_ITEM_ID| PATH_ITEM-----------：|：--------|-------：|：---------|---------:|：---------|：----------------------------2|componentA|4|modulAB|50|，2,4|，componentA，modulAB5|componentA|7|modulAC|100|，5,7|，componentA，modulAC5|componentA|7|modulAC|100|，5,7|，componentA，modulAC5|componentA|11|modulACDE|500|，5,7,11|，componentA，modulAC，modulACDE

db<gt；小提琴这里