求一个sql语句!!!!
t1
a         b    
1         11
2         11
3         34

t2
c         d       e       f
1         0       ac     2
1         1       ax     1
1         2       gf     2
2         0       1       1
2         1       2       1

要输出的结果！
a       b       c         d               e             f
1       11     1         2               gf           2
2       11     2         1               2             1
3       34     null   null         null       null


连接时   t1.a=t2.c
其中   t2的d只取最大的一条


------解决方案--------------------
declare @ta table(a int, b int)
insert @ta select 1, 11
union all select 2, 11
union all select 3, 34

declare @tb table(c int, d int, e varchar(5) , f int)
insert @tb select 1, 0, 'ac ', 2
union all select 1, 1, 'ax ', 1
union all select 1, 2, 'gf ', 2
union all select 2, 0, '1 ', 1
union all select 2, 1, '2 ', 1

select *
from @ta a left join @tb b on a.a=b.c
where not exists(select 1 from @tb where c=b.c and d> b.d)--加一下 <符号

(3 行受影响)

(5 行受影响)
a b c d e f
----------- ----------- ----------- ----------- ----- -----------
1 11 1 2 gf 2
2 11 2 1 2 1
3 34 NULL NULL NULL NULL

(3 行受影响)


------解决方案--------------------
create table t1(a int, b int)
insert t1 select 1, 11
union all select 2, 11
union all select 3, 34
create table t2(c int, d int, e varchar(10), f int)
insert t2 select 1, 0, 'ac ', 2
union all select 1, 1, 'ax ', 1
union all select 1, 2, 'gf ', 2
union all select 2, 0, '1 ', 1
union all select 2, 1, '2 ', 1

select * from t1
left join
(
select * from t2 as tmp
where not exists(select 1 from t2 where c=tmp.c and d> tmp.d)
)t2 on t1.a=t2.c

--result
a b c d e f
----------- ----------- ----------- ----------- ---------- -----------
1 11 1 2 gf 2
2 11 2 1 2 1
3 34 NULL NULL NULL NULL

(3 row(s) affected)