Question

oracle数据库一道面试题

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');
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','赵六');

Answer 1

答案如下,
如果不想与另一个表产关联可以直接decode
with a as(
select distinct h.ci_id, regexp_substr(h.stu_ids, '[^,]+', 1, level) as cl1
from pm_ci h
connect by level <=
length(h.stu_ids) - length(replace(h.stu_ids, ',', '')) + 1), b as (select q.stu_id,
max(q.stu_name) name
from pm_stu q
group by q.stu_id)
select distinct a.ci_id,
wmsys.wm_concat(decode(a.cl1,
'1',
(select b.name
from b
where b.stu_id = '1'),
'2',
(select b.name
from b
where b.stu_id = '2'),
'3',
(select b.name
from b
where b.stu_id = '3'),
'4',
(select b.name
from b
where b.stu_id = '4'))) over(partition by ci_id) tt
from a
第二种方法
select a, c
from (with test as (select t.ci_id a, t.stu_ids c from pm_ci t)
select a,
substr(t.ca,
instr(t.ca, ',', 1, c.lv) + 1,
instr(t.ca, ',', 1, c.lv + 1) -
(instr(t.ca, ',', 1, c.lv) + 1)) AS c
from (select a,
',' || c || ',' AS ca,
length(c || ',') - nvl(length(REPLACE(c, ',')), 0) AS cnt
FROM test) t,
(select LEVEL lv from dual CONNECT BY LEVEL <= 100) c
where c.lv <= t.cnt)
其它的同上面一样,level<=100表示循环次数,这个可以取C的长度除以2
=========================================================================
第三种方法
create or replace function getNote(v_content in varchar2)
return varchar2 is
result varchar2(8000);
cursor res is select * from pm_stu;

begin
result:=v_content;
for i in res loop
result:= replace(result,i.stu_id,i.stu_name);
end loop;
return result;
end getNote;

调用
select a.ci_id,getNote(a.stu_ids) from pm_ci a

Answer 2

你是要面试题呢还是要人解决面试题呢？