怎么实现如下数据刷选

[kisshexuxia]

A id,xm

B id,date1,date2

A 和B的关系是1对多 (1:N)

select a.xm,b.date1,b.date2,a.id from A a,B b where a.id=b.id


结果

id,xm, date1, date2
1, a ,2010-06-02 2010-09-02
1, a ,2010-07-02 2010-09-02
1, a ,2010-08-02 2010-09-02
2, b ,2010-06-02 2010-09-02
3, c ,2010-07-02 2010-09-02
3, c ,2010-08-02 2010-09-02
.
.
.

现在要得出每个ID的date1最大的条数据如下，SQL要怎么写

id,xm, date1, date2

1, a ,2010-08-02 2010-09-02
2, b ,2010-06-02 2010-09-02

3, c ,2010-08-02 2010-09-02

[hao1hao2hao3]

select a.id,a.xm,max(b.date1),b.date2
from A a,B b where a.id=b.id
group by a.id,a.xm,b.date2

[百年树人]

select a.*,b.date1,b.date2
from a,b
where a.id=b.id
and not exists(select 1 from b t where t.id=b.id and t.date1>b.date1)