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

 我有两个表
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 not null drop table [a]gocreate table [a]([id] int,[time] datetime)insert [a]select 1,'2011-12-22 10:45:01.000' union allselect 2,'2011-11-22 10:45:01.000' union allselect 3,'2011-12-22 10:45:01.000'if object_id('[b]') is not null drop table [b]gocreate table [b]([id] int,[time] datetime)insert [b]select 1,'2011-12-22 10:45:01.000' union allselect 1,'2011-11-22 10:45:01.000' union allselect 2,'2011-02-22 10:45:01.000' union allselect 2,'2011-12-22 10:45:01.000' union allselect 3,'2011-11-22 10:45:01.000' union allselect 3,'2011-12-22 10:45:01.000'

select a.id,a.time,b.id,b.time froma left join b on a.id=b.idand not exists(select 1 from b t where t.id=b.id and 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