有点难度的两表查询语句

[碧水幽幽泉]

有两张表:
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高手们指导下！

[inthirties]

B表中也有很上千万条数据吗？

要是性能就太低下叻

[闲人1970]

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

[小灰狼W]

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

[ojuju10]

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

[ojuju10]

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

[嘟嘟xo]

数据太多的话可以考虑建个视图或者中间表，查询语句用#5的就可以了！

[碧水幽幽泉]

B表中有很多字段！
狂浪和永州异蛇的都可以！同是全表扫描！
不过蛇哥的查询时间要少点！

[碧水幽幽泉]

从执行计划中得知！