样例sql:
select gro.*,
regexp_substr(group_name, '[^/]+', 1, 1) as one_group,
regexp_substr(group_name, '[^/]+', 1, 2) as two_group,
regexp_substr(group_name, '[^/]+', 1, 3) as three_group,
cfg.address as address
from app_wam_cfg_group gro
left join (select dbms_lob.substr(wm_concat(office.address)) address,
grop.id
from app_wam_cfg_group grop, app_wam_cfg_office office
where office.id in
(select regexp_substr(grop.work_address_id,
'[^,]+',
1,
rownum)
from dual
connect by rownum <=
length(grop.work_address_id) -
length(replace(grop.work_address_id, ',')) + 1)
group by grop.id) cfg on cfg.id = gro.id
最内层的sql作用为根据将组合id拆分成id的一个集合,可以使用in关键字进行处理;
首先以正则进行字符串截取,因为regexp_substr的第4个参数为分隔组的第几位,由于是不确定的,所以后面通过connect by 来限制分隔组有几个,通过字符串的长度,去掉分隔符,获取长度大小。
倒数第二层则通过wm_concat函数进行合并,dbms_lob.substr函数将clob转换成varchar.
最外一层则为关联