我有一张表BOM,里面存的是物料清单,如下:Parents childs count
------------------------------------------------
F-225-111-A 1-333-895-1 2
F-225-111-A 1-334-895-1 1
F-225-111-A A-987-331-A 4
A-987-331-A 2-875-648-1 1
A-987-331-A A-475-448-A 1
A-475-448-A 4-674-857-9 2parents是父项,child是子项,count是用位,带F开头的是成品,A开头的是半成品,1、2、4是所用到的物料。我用SELECT childs from BOM connect by prior childs=Parents start with Parents='F-225-111-A' 找出'F-225-111-A'所用到的所有物料,如下:1-333-895-1
1-334-895-1
A-987-331-A
2-875-648-1
A-475-448-A
4-674-857-9我想得到如下结果:1-333-895-1
1-334-895-1
2-875-648-1
4-674-857-9展开所有的半成品,得到最终的物料清单,但不包含半成品,我该怎么做?
------------------------------------------------
F-225-111-A 1-333-895-1 2
F-225-111-A 1-334-895-1 1
F-225-111-A A-987-331-A 4
A-987-331-A 2-875-648-1 1
A-987-331-A A-475-448-A 1
A-475-448-A 4-674-857-9 2parents是父项,child是子项,count是用位,带F开头的是成品,A开头的是半成品,1、2、4是所用到的物料。我用SELECT childs from BOM connect by prior childs=Parents start with Parents='F-225-111-A' 找出'F-225-111-A'所用到的所有物料,如下:1-333-895-1
1-334-895-1
A-987-331-A
2-875-648-1
A-475-448-A
4-674-857-9我想得到如下结果:1-333-895-1
1-334-895-1
2-875-648-1
4-674-857-9展开所有的半成品,得到最终的物料清单,但不包含半成品,我该怎么做?
WHERE childs NOT LIKE 'A-%'
connect by prior childs=Parents start with Parents='F-225-111-A'
因为有些半成品是不能拆分的,我们也要把它当成最终的物料,所以用Not like 'A%'这语句行不通。
to zhongchai:
这一句select 1 from BOM where Parents = a.childs里面的1是什么意思,我没有这个字段啊!我想应该要用到connect by语句,否则就展不开子项下面的子项了。
CONNECT BY parent_assembly = PRIOR assembly_id;
ASSEMBLY_ID PARENT_ASSEMBLY LEVEL ASSEMBLY_NAME
----------- --------------- ---------- ----------------------- 200 1 Airplane
201 200 2 Jet Engine
202 200 2 Left Wing
203 200 2 Right Wing 204 200 2 Body
205 204 3 First-Class Seat
207 205 4 Full-Width Cushion
SELECT ASSEMBLY_ID,
RPAD(' ', 2*(LEVEL-1)) || assembly_name assembly_name,
quantity, CONNECT_BY_ISLEAF
FROM bill_of_materials
WHERE LEVEL = 2
START WITH assembly_id = 110
CONNECT BY parent_assembly = PRIOR assembly_id;
ASSEMBLY_ID ASSEMBLY_NAME QUANTITY CONNECT_BY_ISLEAF
----------- ----------------------- ---------- -----------------
111 Piston 6 1
112 Air Filter 1 1
113 Spark Plug 6 1
114 Block 1 1
115 Starter System 1 0
The zeros returned by CONNECT_BY_ISLEAF indicate that none of the assemblies shown in this listing are leaf nodes
minus
select Parents from bills connect by prior childs=Parents start with Parents='F-225-111-A'