求一个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)
