table a:
a_name a_status
mmm 0
nnn 0
lll 1
ddd 0table b:
a_name b_id b_name
mmm 1 xxx
mmm 2 yyy
nnn 1 zzz
nnn 2 kkk
lll 1 ccc
要求查出a_name下b_id最小的那个b_name(没有则为空),且a_status=0,返回结果:
a_name b_name
mmm xxx
nnn zzz
ddd
a_name a_status
mmm 0
nnn 0
lll 1
ddd 0table b:
a_name b_id b_name
mmm 1 xxx
mmm 2 yyy
nnn 1 zzz
nnn 2 kkk
lll 1 ccc
要求查出a_name下b_id最小的那个b_name(没有则为空),且a_status=0,返回结果:
a_name b_name
mmm xxx
nnn zzz
ddd
(select a.a_name,min(b.b_id) b_id from a,b where a.a_name=b.a_name(+) and a.a_status='0' group by a.a_name) t
where t.a_name=b.a_name(+) and t.b_id=b.b_id(+);
from a,b
where a.a_name = b.a_name(+)
and a.a_staus = 0
and b.b_id in (select min(b_id) from b order by a_name group by b_in)