Oracle 根据一行数据的某个字段内容拆行(connect by level)

 今天有个同事问我能不能根据行的指定字段内容进行拆行，例如该行字段内容: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  内容的分号 拆分了成了两行

 

执行计划也是相当优秀的