两个表左连接取数只取一行

 我有两个表
a
id time
1 2011-12-22 10:45:01.000
2 2011-11-22 10:45:01.000
3 2011-12-22 10:45:01.000

b
id time
1 2011-12-22 10:45:01.000
1 2011-11-22 10:45:01.000
2 2011-02-22 10:45:01.000
2 2011-12-22 10:45:01.000
3 2011-11-22 10:45:01.000
3 2011-12-22 10:45:01.000
本来左连接是这么写的
select a.id,a.time,b.id,b.time from
a left join b on a.id=b.id

结果：
id time id time
1 2011-12-22 10:45:01.000 1 2011-12-22 10:45:01.000
1 2011-12-22 10:45:01.000 1 2011-11-22 10:45:01.000
2 2011-11-22 10:45:01.000 2 2011-02-22 10:45:01.000
2 2011-11-22 10:45:01.000 2 2011-12-22 10:45:01.000
3 2011-12-22 10:45:01.000 3 2011-11-22 10:45:01.000
3 2011-12-22 10:45:01.000 3 2011-12-22 10:45:01.000

现在只想取到b表中每个相同id的最后一行数据
结果：
id time id time
1 2011-12-22 10:45:01.000 1 2011-12-22 10:45:01.000
2 2011-11-22 10:45:01.000 2 2011-12-22 10:45:01.000
3 2011-12-22 10:45:01.000 3 2011-12-22 10:45:01.000
求sql怎么写

 

if object_id('[a]')is notnull drop table[a]gocreatetable [a]([id]int,[time]datetime)insert[a]select1,'2011-12-22 10:45:01.000'union allselect2,'2011-11-22 10:45:01.000'union allselect3,'2011-12-22 10:45:01.000'ifobject_id('[b]')is notnull drop table[b]gocreatetable [b]([id]int,[time]datetime)insert[b]select1,'2011-12-22 10:45:01.000'union allselect1,'2011-11-22 10:45:01.000'union allselect2,'2011-02-22 10:45:01.000'union allselect2,'2011-12-22 10:45:01.000'union allselect3,'2011-11-22 10:45:01.000'union allselect3,'2011-12-22 10:45:01.000'

select a.id,a.time,b.id,b.timefroma left join bon a.id=b.idandnot exists(select1from b t where t.id=b.idand t.time>b.time)

/**id time id time----------- ----------------------- ----------- -----------------------1 2011-12-22 10:45:01.000 1 2011-12-22 10:45:01.0002 2011-11-22 10:45:01.000 2 2011-12-22 10:45:01.0003 2011-12-22 10:45:01.000 3 2011-12-22 10:45:01.000(3 行受影响)**/

http://topic.csdn.net/u/20120112/15/74bc9827-ab1b-4cf1-87bf-0763d980b11b.html?seed=32919323&r=77285767#r_77285767