求问一个代码转换的问题

[cody343960591]

分别是字段和对应的代码表
求问如何简单的方法对应的转换
例如01，05这个字段应该转换为中药材，化学药制剂

[cody343960591]

结啦结啦 谢谢~

[cody343960591]

引用 2 楼 qq646748739 的回复:

没问题的话，此楼结贴～

刚才突然发现了一个问题 如果JYFW为空的时候 因为只是这个字段为空 但是这样没有数据了呀？

[碧水幽幽泉]

--1.创建测试表
create table t1 as
select 1 id, '01,05' jyfw from dual union all
select 2 id, '05,09' jyfw from dual union all
select 3 id,'05' jyfw from dual union all
select 4 id, '01' jyfw from dual union all
select 5 id, '01,09' jyfw from dual union all
select 6 id, '02,07,12' jyfw from dual

create table t2 as
select '01' code,'中药材' name from dual union all
select '02' code,'中药饮片(定型包装)' name from dual union all
select '03' code,'中成药' name from dual union all
select '04' code,'化学原料药' name from dual union all
select '05' code,'化学药制剂' name from dual union all
select '06' code,'抗生素' name from dual union all
select '07' code,'生化药品' name from dual union all
select '08' code,'生物制品' name from dual union all
select '09' code,'生物制品(限诊断药品)' name from dual union all
select '12' code,'其他药品' name from dual 

--2.sql实现
--方案1:
select t1.id,to_char(wm_concat(t2.name)) name
 from(select id,trim(regexp_substr(jyfw,'[^,]+',1,level)) as code
		from t1
	  connect by id = prior id 
		  and prior dbms_random.value is not null
		  and level <= length(regexp_replace(jyfw, '[^,]'))+1
	 ) t1,t2
 where t1.code = t2.code
group by t1.id;

--方案2:
select t1.id,to_char(wm_concat(t2.name)) name
 from(select distinct id,
             substr(','||jyfw||',',instr(','||jyfw||',', ',', 1, level) + 1, instr(','||jyfw||',', ',', 1, level + 1) -( instr(','||jyfw||',', ',', 1, level) + 1)) as code
        from t1
      connect by level <= regexp_count(jyfw,',')+1
	 ) t1,t2
 where t1.code = t2.code
group by t1.id;

[碧水幽幽泉]

没问题的话，此楼结贴～