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 ^.^
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;
用等于的话,把caid限制成了一个值,把等于改成in就行 select * from tableA where caid in( select caid from tableB where wtforagent<=20) order by bgtime asc;
用那么麻烦吗?? select * from tableA a , ( select caid from tableB where wtforagent<=20) b where a.caid = b.caid order by bgtime asc;
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; 这两种都能达到你要的效果, 如果表中的数据不是很大话,两种任意选一种。 但是数据量大的话,推荐第一种。
select * from tableA t where exists ( select 1 from tableB where t.caid=caid and wtforagent<=20) order by bgtime asc;个人喜欢用exists
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;
用等于的话,把caid限制成了一个值,把等于改成in就行
select * from tableA where caid in( select caid from tableB where wtforagent<=20) order by bgtime asc;
select * from tableA a ,
( select caid from tableB where wtforagent<=20) b
where a.caid = b.caid order by bgtime asc;
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;
这两种都能达到你要的效果,
如果表中的数据不是很大话,两种任意选一种。
但是数据量大的话,推荐第一种。
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~!