有一个表为BOM表,结构如下:ID FITM CITM
1 A A1
2 A A2
3 B A1
4 B A2
5 B A
6 C A1
7 C C1
8 C B
...还有一个表为属性表,结构如下:
ID ITM NATURE
1 A SC
2 B SC
3 A1 CG
4 A2 CG
5 C SC
说明:
1)FITM为产品父件,CITM为产品子件,A、B、C以及A1、A2等都为字符串型,A、B、C为生产类属性产品,A1、A2为采购类属性产品
2)SC即生成,CG即采购求:筛选出 父产品(如,A、B、C等)的子件(如,A1、B1、C1等)中都是采购类属性的结果(应该只筛选出A)求助大家,如何写这个语句啊?
1 A A1
2 A A2
3 B A1
4 B A2
5 B A
6 C A1
7 C C1
8 C B
...还有一个表为属性表,结构如下:
ID ITM NATURE
1 A SC
2 B SC
3 A1 CG
4 A2 CG
5 C SC
说明:
1)FITM为产品父件,CITM为产品子件,A、B、C以及A1、A2等都为字符串型,A、B、C为生产类属性产品,A1、A2为采购类属性产品
2)SC即生成,CG即采购求:筛选出 父产品(如,A、B、C等)的子件(如,A1、B1、C1等)中都是采购类属性的结果(应该只筛选出A)求助大家,如何写这个语句啊?
with bz as (select distinct fitm,(select nature from sxb where sxb.itm=bom.citm) nature from bom)
select * from bom where not exist (select 1 from bz where bz.fitm=bom.fitm and nature='SC') order by id;
(select 1 id, 'A' fitm, 'A1' citm
from dual
union all
select 2 id, 'A' fitm, 'A2' citm
from dual
union all
select 3 id, 'B' fitm, 'A1' citm
from dual
union all
select 4 id, 'B' fitm, 'A2' citm
from dual
union all
select 5 id, 'B' fitm, 'A' citm
from dual
union all
select 6 id, 'C' fitm, 'A1' citm
from dual
union all
select 7 id, 'C' fitm, 'C1' citm
from dual
union all
select 8 id, 'C' fitm, 'B' citm from dual),
POR AS
(select 1 ID, 'A' ITM, 'SC' NATURE
from dual
union all
select 2 ID, 'B' ITM, 'SC' NATURE
from dual
union all
select 3 ID, 'A1' ITM, 'CG' NATURE
from dual
union all
select 4 ID, 'A2' ITM, 'CG' NATURE
from dual
union all
select 5 ID, 'C' ITM, 'SC' NATURE from dual)
select distinct fitm
from bom
minus
select bom.fitm
from bom
where not exists (select itm
from por
where por.nature = 'CG'
and por.ITM = BOM.CITM)