select a.*,b.* from a right outer join b on a.a_id=b.a_id order by b.a_id
假设你需要得到的b表的字段为fd1,fd2。select a.a_id, t1.fd1,b.fd2, t2.fd1.c.fd2 from a, (select b.a_id,b.fd1,b.fd2 from b, (select a_id,max(b_order) as max_b_order from b group by a_id) as bmax where b.a_id=bmax.a_id and b.b_order=bmax.max_b_order) as t1, (select b.a_id,b.fd1,b.fd2 from b, (select a_id,min(b_order) as min_b_order from b group by a_id) as bmin where b.a_id=bmin.a_id and b.b_order=bmin.min_b_order) as t2 where a.a_id = b.a_id and a.a_id = t1.a_id and a.a_id = t2.a_id 其中:1. (select a_id,max(b_order) as max_b_order from b group by a_id) as bmax 得到b_order较大的那些记录的a_id和b_order.2. (select b.a_id,b.fd1,b.fd2 from b, (select a_id,max(b_order) as max_b_order from b group by a_id) as bmax where b.a_id=bmax.a_id and b.b_order=bmax.max_b_order) as t1, 得到b_order较大的那些记录的a_id和fd1,fd2
这个问题没有必要用外连接吧! select * from a,b where a.id=b.id order by a.id,b.order desc
select a.field1,b.field2,b.field3,…… from a join b on a.a_id=b.a_id order by a.a_id,b_order desc 如果a,b有同名字段的话,请不要*号。
不需要这么复杂吧!我觉得wwl007(疑难杂症)的方法就行。不过可能要稍加改动:select b.* from a,b where a.id=b.id order by a.id,b.order desc
9494iltg说的不错。诸位高人把一个小问题想的也太复杂了。
select a.*,test.* from a,(select * from b group by b_order order by b_order)"test" where a.a_id=test.a_id;
t1.fd1,b.fd2,
t2.fd1.c.fd2
from a, (select b.a_id,b.fd1,b.fd2
from b,
(select a_id,max(b_order) as max_b_order from b group by a_id) as bmax
where b.a_id=bmax.a_id and b.b_order=bmax.max_b_order) as t1, (select b.a_id,b.fd1,b.fd2
from b,
(select a_id,min(b_order) as min_b_order from b group by a_id) as bmin
where b.a_id=bmin.a_id and b.b_order=bmin.min_b_order) as t2 where a.a_id = b.a_id and a.a_id = t1.a_id and a.a_id = t2.a_id
其中:1. (select a_id,max(b_order) as max_b_order from b group by a_id) as bmax
得到b_order较大的那些记录的a_id和b_order.2. (select b.a_id,b.fd1,b.fd2
from b,
(select a_id,max(b_order) as max_b_order from b group by a_id) as bmax
where b.a_id=bmax.a_id and b.b_order=bmax.max_b_order) as t1,
得到b_order较大的那些记录的a_id和fd1,fd2
select * from a,b where a.id=b.id order by a.id,b.order desc
如果a,b有同名字段的话,请不要*号。
from a,(select * from b
group by b_order
order by b_order)"test"
where a.a_id=test.a_id;