Oracle中split功能的实现

原始需求:

有这样的表：tb和pk两列

PK是将表的多个主键用“|”进行分隔，我想把它变成多行

如

fundamentals_asia1_af_out

ID_BB_GLOBAL|BC_DT|BC_EQY_DVD_ADJUST_FACT|BC_EQY_DVD_ADJ_FUND_OPER|BC_EQY_DVD_ADJ_FUND_FLAG

 

 

变成：

fundamentals_asia1_af_out	ID_BB_GLOBAL
fundamentals_asia1_af_out	BC_DT
fundamentals_asia1_af_out	BC_EQY_DVD_ADJUST_FACT
fundamentals_asia1_af_out	BC_EQY_DVD_ADJ_FUND_OPER
fundamentals_asia1_af_out	BC_EQY_DVD_ADJ_FUND_FLAG

 

 

 

 

 

 

 

 

 

 

 

方案一

with t_n as(
select rownum as N from dual
connect by rownum<=200
)
,cte as (
      select 'equity_asia1_dif' tb,'ID_BB_GLOBAL|RC' pk from dual union all       
      select 'fundamentals_asia1_af_out','ID_BB_GLOBAL|BC_DT|BC_EQY_DVD_ADJUST_FACT|BC_EQY_DVD_ADJ_FUND_OPER|BC_EQY_DVD_ADJ_FUND_FLAG' from dual union all
      select 'fundamentals_asia1_bs_out','ID_BB_GLOBAL|FISCAL_YEAR_PERIOD|FILING_STATUS|EQY_CONSOLIDATED|ACCOUNTING_STANDARD' from dual
),res as (
  select a.*,b.*,nvl(lag(b.n) over(partition by a.tb order by b.n),0) as head
  from cte a inner join t_n b on length(a.pk||'|')>=b.n
  where substr(a.pk||'|',b.n,1)='|'
)
select tb,substr(pk,head+1,n-head-1),pk
from res
order by tb,n;

用到的知识点：

• l  公用表表达式：with xxx as ()
• l  Connect by : 我这里用它来简单的构建了一个1-50的序号。可以百度它的高级用法
• l  Lag() over():开窗函数
• l  利用序号表进行拆分(实际是个技巧,它还有很多的妙用)

方案二(正则)

with a as (
select 'equity_asia1_dif' tb,'ID_BB_GLOBAL|RC' pk from dual union all      
      select 'fundamentals_asia1_af_out','ID_BB_GLOBAL|BC_DT|BC_EQY_DVD_ADJUST_FACT|BC_EQY_DVD_ADJ_FUND_OPER|BC_EQY_DVD_ADJ_FUND_FLAG' from dual union all
      select 'fundamentals_asia1_bs_out','ID_BB_GLOBAL|FISCAL_YEAR_PERIOD|FILING_STATUS|EQY_CONSOLIDATED|ACCOUNTING_STANDARD' from dual
)
select  t1.tb,regexp_substr(pk,'[^|]+',1,t2.lv) pk from a t1,
(select level lv from (select max(regexp_count(pk,'[^|]+',1))+1 ct from a) b connect by level<b.ct) t2
where regexp_substr(pk,'[^|]+',1,t2.lv) is not null
order by 1,t2.lv