[求教]双表多值select问题

[山书生]

select * from tableA where caid=( select caid from tableB where wtforagent<=20) order by bgtime asc;

请教如何在Oracle实现以上效果，从表tableB中select出符合条件的caid（多个值），然后从tableB中select出对应caid的各条记录...

thx everyone ^.^

[zhangwonderful]

select * from tableA a where exists ( select b.caid from tableB b where a.caid=b.caid and b.wtforagent<=20) order by bgtime asc;

[心中的彩虹]

select * from tableA where caid in( select caid from tableB where wtforagent<=20) order by bgtime asc;

[心中的彩虹]

select * from tableA t where exists( select 1 from tableB where t.caid=caid  wtforagent<=20) order by bgtime asc;

[baobeizhu100]

用等于的话，把caid限制成了一个值，把等于改成in就行
select * from tableA where caid in( select caid from tableB where wtforagent<=20) order by bgtime asc;

[lzbbob1985]

用那么麻烦吗？？


select * from tableA a ,
( select caid from tableB where wtforagent<=20) b
where a.caid = b.caid order by bgtime asc;

[header230]

     select * from tableA t where exists
          ( select 1 from tableB where t.caid=caid  and wtforagent<=20) 
     order by bgtime asc;

     select * from tableA where caid in 
     ( select caid from tableB where wtforagent<=20)    
     order by bgtime asc;

这两种都能达到你要的效果，
如果表中的数据不是很大话，两种任意选一种。
但是数据量大的话，推荐第一种。

[rains0929]

赞同5楼的写法

[xdy3008]

select * from tableA t where exists
          ( select 1 from tableB where t.caid=caid  and wtforagent<=20) 
     order by bgtime asc;

个人喜欢用exists

[山书生]

谢谢各位方家达人~:)
have a nice weekend~!