有一张表 aa
clmc clwz
xp1 a
xp2 b
xp3 c
yl1 xp1,xp2
yl2 xp3
xp4 a
xp5 b
xp6 c
yl1 xp4,xp5,xp6请帮忙整合成
clmc clwz qita
xp1 a yl1
xp2 b yl1
xp3 c yl2
xp4 a yl1
xp5 b yl1
xp6 c yl1
clmc clwz
xp1 a
xp2 b
xp3 c
yl1 xp1,xp2
yl2 xp3
xp4 a
xp5 b
xp6 c
yl1 xp4,xp5,xp6请帮忙整合成
clmc clwz qita
xp1 a yl1
xp2 b yl1
xp3 c yl2
xp4 a yl1
xp5 b yl1
xp6 c yl1
(select clmc from aa b where instr(b.clwz,a.clmc)>0) qita
from aa a
where clmc like 'xp%';
select 'xp1' clmc,'a' clwz from dual
union all
select 'xp2' clmc,'b' clwz from dual
union all
select 'xp3' clmc,'c' clwz from dual
union all
select 'yl1' clmc,'xp1,xp2' clwz from dual
union all
select 'yl2' clmc,'xp3' clwz from dual
union all
select 'xp4' clmc,'a' clwz from dual
union all
select 'xp5' clmc,'b' clwz from dual
union all
select 'xp6' clmc,'c' clwz from dual
union all
select 'yl1' clmc,'xp4,xp5,xp6' clwz from dual
)
select clmc, clwz,
(select clmc from temp t where instr(t.clwz,tp.clmc)>0) qita
from temp tp
where clmc like 'xp%'结果
1 xp1 a yl1
2 xp2 b yl1
3 xp3 c yl2
4 xp4 a yl1
5 xp5 b yl1
6 xp6 c yl1
union all
select 'yl2' clmc,'xp3' clwz from dual
这yl1—xp1,xp2
yl2—xp3 是不定项啊 不能直接这么写的啊
有的型号产品 yl1—xp3 那我一句句这么写 要烦死的啊
...楼主只需要把下面的那句执行即可汗,他上面的是造数据用的你不用执行
select clmc, clwz,
(select clmc from temp t where instr(t.clwz,tp.clmc)>0) qita
from temp tp
where clmc like 'xp%'
a.clwz,
case
when a.clwz in ('a','b') then 'yl1'
when a.clwz='c' and a.clmc='xp3' then 'yl2'
else 'yl1'
end as qita
from aa a where a.clwz in ('a','b','c')