Oracle多值字典解析
问题描述:
一般情况下,业务数据中存储的字典值为单值,比如"1"或"2",直接关联字典表后,可以解析出字典值;
但有时因业务要求,需要存储多选值,比如:“1,2,11,12”,此时,无法直接关联字典表进行解析;
本文讲解如下通过函数,进行多值字典解析;
适用范围
oracle 11g
业务字典表:
GUPID | GUPNAME |
---|---|
1 | 分组1 |
2 | 分组2 |
3 | 分组3 |
4 | 分组4 |
5 | 分组5 |
11 | 分组11 |
12 | 分组12 |
业务数据表:
ID | GUPIDS |
---|---|
1 | 1,2,3 |
2 | 1,11,12 |
3 | 4,5 |
最终需要解析成如下结果:
ID | GUPID | GUPNAME |
---|---|---|
1 | 1 | 分组1 |
1 | 2 | 分组2 |
1 | 2 | 分组3 |
2 | 1 | 分组1 |
2 | 11 | 分组11 |
2 | 12 | 分组12 |
3 | 4 | 分组4 |
3 | 5 | 分组5 |
解决办法:
通过组合使用 CONNECT BY LEVEL,regexp_substr 语法可以解析出字典内容。
/* 业务字典表 */
create table tb_dic as
select 1 as gupId, '分组1' as gupName from dual union all
select 2 as gupId, '分组2' as gupName from dual union all
select 3 as gupId, '分组3' as gupName from dual union all
select 4 as gupId, '分组4' as gupName from dual union all
select 5 as gupId, '分组5' as gupName from dual union all
select 11 as gupId, '分组11' as gupName from dual union all
select 12 as gupId, '分组12' as gupName from dual
;
/* 业务数据表 */
create table tb_data as
select 1 as id, '1,2,3' as gupIds from dual union all
select 2 as id, '1,11,12' as gupIds from dual union all
select 3 as id, '4,5' as gupIds from dual
;
/* 分析业务数据中,存在的字典值数 */
select x1.id, x1.gupIds, x0.gupId, x0.gupName
from tb_dic x0,
(select id, gupIds,length(gupIds) - length(replace(gupIds, ',')) + 1 as dics
from tb_data
) x1 ,
(/* 用于生成连续数值 */
select LEVEL as rid from dual CONNECT BY LEVEL <= (select max(gupId) from tb_dic x0)
) x2
where regexp_substr(x1.gupIds, '[^,]+',1,x2.rid)=x0.gupId
and x2.rid <=x1.dics
order by 1,3
;
或者,整合上面的内容为一条sql,输出结果一样,如下:
with x0 as (/* 业务字典表 */
select 1 as gupId, '分组1' as gupName from dual union all
select 2 as gupId, '分组2' as gupName from dual union all
select 3 as gupId, '分组3' as gupName from dual union all
select 4 as gupId, '分组4' as gupName from dual union all
select 5 as gupId, '分组5' as gupName from dual union all
select 11 as gupId, '分组11' as gupName from dual union all
select 12 as gupId, '分组12' as gupName from dual
), x1 as (/* 业务数据表 */
select 1 as id, '1,2,3' as gupIds from dual union all
select 2 as id, '1,11,12' as gupIds from dual union all
select 3 as id, '4,5' as gupIds from dual
), x2 as (/* 分析业务数据中,存在的字典值数 */
select id, gupIds,length(gupIds) - length(replace(gupIds, ',')) + 1 as dics
from x1
)
select x2.id, x2.gupIds, x0.gupId, x0.gupName
from x2,x0,
(/* 用于生成连续数值 */
select LEVEL as rid from dual CONNECT BY LEVEL <= (select max(gupId) from x0)
) t
where regexp_substr(x2.gupIds, '[^,]+',1,t.rid)=x0.gupId
and t.rid <=x2.dics
order by 1,3