今天有个同事问我能不能根据行的指定字段内容进行拆行，例如该行字段内容:2C18E570;2C18DE70 根据 分号拆出两行
一眼望穿,非常简单毕竟从事Oracle 已经10多年了,比这个更变态的需求都遇到过,归根还得要对 Connect by level 运用非常熟悉
select eco_number,
assembly_item,
so_number,
no_impl_wo,
substr(wo_number /*替换拆分的列*/,
instr(wo_number /*替换拆分的列*/, ';', 1, levels.lvl) + 1,
instr(wo_number /*替换拆分的列*/, ';', 1, levels.lvl + 1) -
(instr(wo_number /*替换拆分的列*/, ';', 1, levels.lvl) + 1)) as test1 /*替换拆分的列*/
from (select cev.eco_number,
cev.assembly_item,
cev.so_number,
cev.no_impl_wo,
--在这里增加需要显示的字段
';' || wo_number /*替换拆分的列*/
|| ';' as wo_number /*替换拆分的列*/,
length(wo_number /*替换拆分的列*/) -
nvl(length(replace(wo_number /*替换拆分的列*/, ';')), 0) + 1 as cnt
from cux_eco_update_woso_v cev /*替换表*/
) a,
(select rownum as lvl
from (select max(length(wo_number /*替换拆分的列*/
|| ';') -
nvl(length(replace(wo_number /*替换拆分的列*/, ';')),
0)) max_len
from cux_eco_update_woso_v /*替换表*/
)
connect by level <= max_len) levels
where levels.lvl <= a.cnt
order by eco_number;
效果：
相同的：
select eco_number,
assembly_item,
so_number,
no_impl_wo,
ssx,
substr(wo_number /*替换拆分的列*/,
instr(wo_number /*替换拆分的列*/, ';', 1, levels.lvl) + 1,
instr(wo_number /*替换拆分的列*/, ';', 1, levels.lvl + 1) -
(instr(wo_number /*替换拆分的列*/, ';', 1, levels.lvl) + 1)) as test1 /*替换拆分的列*/
from (select cev.eco_number ,
cev.assembly_item,
cev.so_number,
cev.no_impl_wo,
cev.wo_number ssx,
--在这里增加需要显示的字段
';' || wo_number /*替换拆分的列*/
|| ';' as wo_number /*替换拆分的列*/,
length(wo_number /*替换拆分的列*/) -
nvl(length(replace(wo_number /*替换拆分的列*/, ';')), 0) + 1 as cnt
from cux_eco_update_woso_v cev /*替换表*/
) a,
(select rownum as lvl
from (select max(length(wo_number /*替换拆分的列*/
|| ';') -
nvl(length(replace(wo_number /*替换拆分的列*/, ';')),
0)) max_len
from cux_eco_update_woso_v /*替换表*/
)
connect by level <= max_len) levels
where levels.lvl <= a.cnt
order by eco_number
结果：根据 2C18E570;2C18DE70 内容的分号 拆分了成了两行
标签：level,wo,number,cev,connect,拆分,拆行,替换,select From： https://www.cnblogs.com/ivenlin/p/18121204