我现在有一个字段是存：1,2,3的，而它对应另一张值集表中。eg;
课程人员表
id name Course
1 张三 1，2，3
值集表
code name
1 语文
2 数字
3 英语
………………
course 与code 关联 如何查询出结果为：
张三 语文,数字,英语
各位大侠帮帮在下啊。
Oracle 字段拆分
------解决方案--------------------
小数据量可以 大了可以考虑存储
with t1 as
(
select 1 id,'张三' name,'1,2,3' course from dual union all
select 2 id,'李四' name,'1,3,4' course from dual
),t2 as
(
select 1 code,'语文' name from dual union all
select 2 code,'数字' name from dual union all
select 3 code,'英语' name from dual union all
select 4 code,'历史' name from dual
)
select peo,wm_concat(c_name) c_name
from
(
select t.id,t.name peo,t2.code,t2.name c_name
from
(
select distinct id,name,replace(regexp_substr(course,'[^,]+',1,level),',',' ') course
from t1
connect by level<=length(course)-length(replace(course,',',''))+1
) t,t2
where t.course = t2.code
)
group by peo
peo c_name
--------------------------------------
1 李四 语文,英语,历史
2 张三 语文,英语,数字
------解决方案--------------------
with t1 as
(select '1' id, '张三' name, '1,2,3' course
from dual
union
select '1' id, '李四' name, '2,4' course from dual),
t2 as
(select '1' code, '语文' name
from dual
union
select '2' code, '数学' name
from dual
union
select '3' code, '英语' name
from dual
union
select '4' code, '物理' name from dual)
select t1.name, dbms_lob.substr(wm_concat(t2.name))
from t1, t2
where instr(','
------解决方案--------------------
t1.course
------解决方案--------------------