日期:2014-05-17  浏览次数:20837 次

怎么把子表查询数据作为一个条件?
比如有这样一个表:
A B 
1 2 
3 1 
4 2
6 1 
我先做一个子表查询b项等于2中a项最大的。然后把这个作为条件查询b项等于1中a项最小的,但必须大于子表查询出的值,怎么办?

------解决方案--------------------
SQL code

select max(t2.a) amax,t2.b from (
select t.a,t.b from 
t,(select b,max(a) amax from t where b=2 group by b) t1
where t.b=1 and t.a>t1.amax) t2
group by t2.b;

------解决方案--------------------
SQL code

with t1 as
(
     select 1 c1,2 c2 from dual
     union all
     select 3 c1,1 c2 from dual
     union all
     select 4 c1,2 c2 from dual
     union all
     select 6 c1,1 c2 from dual
)

select min(c1) c1
from t1
where c2 = 1 and c1 > (select max(c1) from t1 where c2 = 2)


     c1
------------------
1    6