某表的一个字段是1,2,3这种形式，这是代码值，想转换成中文
某表的一个字段是1,2,3这种形式，苹果,梨子,香蕉
tableA
fieldA
1,2,3
tableB
name code
苹果 1
梨子 2
香蕉 3
[解决办法]
select a.a,b.name from a ,b where a.a = b.code ;
with t1 as
(
select 1 c1,'1,2,3' c2 from dual union all
select 2 c1,'4,5' c2 from dual
),t2 as
(
select 1 c1,'苹果' c2 from dual union all
select 2 c1,'香蕉' c2 from dual union all
select 3 c1,'梨子' c2 from dual union all
select 4 c1,'椰子' c2 from dual union all
select 5 c1,'橙子' c2 from dual
)
select t.c1,wm_concat(t2.c2) c2
from
(
select distinct c1,regexp_substr(c2, '[^,]+',1,level) c2
from t1
connect by level <= length(c2) - length(replace(c2,',','')) + 1
) t,t2
where t.c2 = t2.c1
group by t.c1
c1 c2
---------------------------------
11苹果,香蕉,梨子
22椰子,橙子
