这新Sql怎样写?

[lusee]

现在有几个表A1（表示基本信息）A0102，....ID(关键字)
A0102 ID
AA1 1
BB1 2

B1(B0102,...,id(对应于A1表的Id,),ID2(关键字)）
B0102 ....B0120（毕业时间） ID ID2
大学毕业 2000-06-31 1 1
研究生毕业 2003-06-31 1 2
大专毕业 1995-06-31 2 3
大学毕业 2000-06-31 2 4

B2(B0202,...,id(对应于A1表的Id,),ID2(关键字)）
B0202.... B0220（取得时间） ID ID2
助理工程师 2000-09-12 1 1
工程师 2001-04-51 1 2
技术员 1995-01-12 2 3
高级工程师 2003-12-15 2 4

现要得到记录
A0102 B0102 B0120 B0202 B0220
AA1 研究生毕业 2003-06-31 工程师 2001-04-51
BB1 大学毕业 2000-06-31 高级工程师 2003-12-15

[zjcxc]

select a.A0102,b.B0120,b.B0120,c.B0202,c.B0220
from A1 a
join B1 b on a.id=b.id
and b.id2=(select top 1 id2 from B1 where id=b.id order by B0120 desc)
join B2 c on a.id=c.id
and c.id2=(select top 1 id2 from B2 where id=c.id order by B0220 desc)

[zjcxc]

select a.A0102,b.B0120,b.B0120,c.B0202,c.B0220
from A1 a
join (
select a.id,id2=max(a.id2)
from B1 a join(
select id,B0120=max(B0120)
from b1 group by id
)b on a.id=b.id and a.B0120=b.B0120
group by a.id
)b1 on a.id=b.id
join B1 b on b.id=b1.id and b.id2=b1.id2
join (
select a.id,id2=max(a.id2)
from B2 a join(
select id,B0220=max(B0220)
from b2 group by id
)b on a.id=b.id and a.B0220=b.B0220
group by a.id
)c1 on a.id=c.id
join B2 c on c.id=c1.id and c.id2=c1.id2

[CSDNM]

select a1.A0102,x.B0102,x.B0120,y.B0202,y.B0220
from a1 left join (select B0102,B0120,id from b1 t where id2=(select max(id2) from b1 where id=t.id)) as x on a1.id=x.id
left join (select B0202,B0220,id from b2 t1 where id2=(select max(id2) from b2 where id=t1.id)) as y on a1.id=y.id

[CSDNM]

呵呵，动作太慢