Question

关于oracle数据库的一道面试题，使用wm_concat()主函数完成

课程表：ci_id表示课程，stu_ids表示选课的学生
create table pm_ci (
ci_id varchar(20) primary key,
stu_ids varchar(100)
);
insert into pm_ci values('1','1,2,3,4');
insert into pm_ci values('2','1,4');
学生表：stu_id 学生ID，stu_name 学生姓名
create table pm_stu (
stu_id varchar(20) primary key,
stu_name varchar(20)
);
insert into pm_stu values('1','张三');
insert into pm_stu values('2','李四');
insert into pm_stu values('3','王五'):
insert into pm_stu values('4','赵六');
要求使用wm_concat()主函数，输出如下结果

哪位大侠帮助解答下呀，很是着急啊





Answer 1

WITH div_pm_ci AS(
select ci_id, substr( stu_ids, instr(','|| stu_ids, ',', 1, rn),
instr( stu_ids||',', ',', 1, rn) - instr(','|| stu_ids, ',', 1, rn) ) stu_id
from pm_ci,(select rownum rn from dual connect by rownum < 10)
where instr(','|| stu_ids, ',', 1, rn) > 0)
SELECT a.ci_id,wm_concat(b.stu_name) stu_name
FROM div_pm_ci a,pm_stu b
WHERE a.stu_id=b.stu_id
GROUP BY a.ci_id