oracle数据库一道面试题

createtablepm_ci(ci_idvarchar(20)primarykey,stu_idsvarchar(100));insertintopm_civalue... 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','赵六');
展开
 我来答
hqbzl
2011-09-20 · 超过20用户采纳过TA的回答
知道答主
回答量:56
采纳率:0%
帮助的人:54.6万
展开全部
答案如下,
如果不想与另一个表产关联可以直接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
enjoy恒恒
2011-09-19
知道答主
回答量:16
采纳率:0%
帮助的人:8.6万
展开全部
你是要面试题呢还是要人解决面试题呢?
已赞过 已踩过<
你对这个回答的评价是?
评论 收起
推荐律师服务: 若未解决您的问题,请您详细描述您的问题,通过百度律临进行免费专业咨询

为你推荐:

下载百度知道APP,抢鲜体验
使用百度知道APP,立即抢鲜体验。你的手机镜头里或许有别人想知道的答案。
扫描二维码下载
×

类别

我们会通过消息、邮箱等方式尽快将举报结果通知您。

说明

0/200

提交
取消

辅 助

模 式