帮忙解决一下oracle
tableA
id type
1 2,3
2 4,5常量表
constant
id name
2 类型2
3 类型3
4 类型4
5 类型5
要求查询结果
1 类型2,类型3
2 类型4,类型5
tableA
id type
1 2,3
2 4,5常量表
constant
id name
2 类型2
3 类型3
4 类型4
5 类型5
要求查询结果
1 类型2,类型3
2 类型4,类型5
id type
1 2,3
2 4,5type 里面的 2,3 存储到一个字段里??
1 2,3
2 4,5为什么不这样存储id type
1 2
1 3
2 4
2 5
create table tableA( tid int,type char(9));
insert into tableA values(1,'2,3');
insert into tableA values(2,'4,5');
commit;drop table constantT;
create table constantT( cid int,name char(9));
begin
for i in 2..5 loop
insert into constantT values(i,'类型'||i);
commit;
end loop;
end;drop table rest;
create table rest( tid int,name varchar(9) );declare id constantT.Cid%type;
cursor usr_cor IS
select constantT.Cid from constantT ;
begin
open usr_cor ;
loop
fetch usr_cor into id;
exit when usr_cor%notfound;
insert into rest (select distinct t1.tid ,t2.name from tableA t1,constantT t2 where t1.type like '%'||id||'%' and t2.cid =id ) ;---'%'||id||'%';
commit;
end loop;
close usr_cor;
end; drop table myresult ;
create table myresult( tid int,name varchar(90) );
----select distinct (rt.tid) from rst rtdeclare
jname varchar(90);
cid int;
tname rest.name%type;
cursor usr_cid IS
select distinct (rt.tid) from rest rt ;
cursor usr_cor IS
select rest.name from rest where rest.tid=cid ;
begin
open usr_cid ;
loop
fetch usr_cid into cid;
exit when usr_cid%notfound;
open usr_cor ;
loop
fetch usr_cor into tname;
exit when usr_cor%notfound;
jname := jname||tname;
end loop;
close usr_cor;
DBMS_OUTPUT.PUT_LINE(jname);
insert into myresult values(cid,jname);
jname:=null;
end loop;
close usr_cid;
end;
select * from myresult;