有点难度的两表查询语句

Question

有两张表:
A表字段: no  number, roles varchar2(40)          --A表中roles为B表id的集合
B表字段: id  number, positionname varchar2(20)A.B表中有很上千万条数据,先列举几条:
A表数据:
   insert into a (id, roles) values(1,'1,2,3');B表数据:
   insert into b (id, positionname ) values(1,'项目总监');
   insert into b (id, positionname ) values(2,'项目经理');
   insert into b (id, positionname ) values(3,'开发人员');现需求：根据A表中的roles字段,查询对应的id和positionname
 如A中roles为'1,2,3'
 则要求查询出来的结果为:
     id    positionname
      1    项目总监
      2    项目经理
      3    开发人员我采用循环去做的，结果总只显示最后一条对应的数据。望各位Oracle高手们指导下！

Answer 1

这是一个典型的一对多的例子,B表应该是一个代码字典，不会有太多数据。
可以使用动态执行的SQL语句去查询。如果讲效率应该改造A表，让roles字段同B表中的id对应，然后使用连接即可。

Answer 2

只是要查出b表中在A表出现的记录的话
select * from b
where exists(select 1 from a
  where ','||roles||',' like '%,'||b.id||',%') 

Answer 3

 create table a(id int,roles varchar2(10));
 insert into a (id, roles) values(1,'1,2,3');  create table b(id int,positionname varchar2(10));
 insert into b (id, positionname ) values(1,'项目总监'); 
 insert into b (id, positionname ) values(2,'项目经理'); 
 insert into b (id, positionname ) values(3,'开发人员'); 
 commit;
 select b.* from a,b
 where instr(a.roles,b.id)>0 
 

Answer 4

 create table a(id int,roles varchar2(10));
 insert into a (id, roles) values(1,'1,2,3');  create table b(id int,positionname varchar2(10));
 insert into b (id, positionname ) values(1,'项目总监'); 
 insert into b (id, positionname ) values(2,'项目经理'); 
 insert into b (id, positionname ) values(3,'开发人员'); 
 commit;
 --两边加上','，防止出现类似1和11匹配情况
 select b.* from a,b
 where instr(','||a.roles||',',','||b.id||',')>0