这个SQL语句 如何写
表A有字段A1,A2
记录
A1         A2
1           21
2           22
3           23
表B有字   A1,B3
记录
A1         B2
1           31
2           32
2           33
2           34
将两个表中记录合并,表B中外键A1重复记录只出现一次(MAX(B2))
要求结果:
A1       A2       B3
1         21       31
2         22       34
3         23        

这个查询如体写???    


------解决方案--------------------
select * from a a join (select a1,max(b1)as b1 from b group by a1)b on a.a1=b.b1
------解决方案--------------------

create table a
(
a1 int,
a2 int
)

create table b
(
b1 int,
b2 int
)

insert into a select 1, 21
insert into a select 2, 22
insert into a select 3, 23

insert into b select 1, 31
insert into b select 2, 32
insert into b select 2, 33
insert into b select 2, 34

--语句
select aa.a1,aa.a2,bb.b2
from a aa left join b bb on aa.a1 = bb.b1
where not exists(select 1 from b where b1 = bb.b1 and b2 > bb.b2)

--结果
1 21 31
2 22 34
3 23 NULL

------解决方案--------------------
--try
表b有主键没，没的话加个主键ＩＤ
select a.A1,a.A2,b.B3 from A a left join
(select * from b c where c.id=(select top 1 id from b where c.A1=A1 desc B2)) b
on a.A1=b.A1
------解决方案--------------------
declare @a table(a1 int,b1 int)
insert into @a select 1,21 union all
select 2,22 union all
select 3,23
declare @b table(a1 int,b2 int)
insert into @b select 1,31 union all
select 2,32 union all
select 2,33 union all
select 2,34
select a.a1,a.b1,b.b1 from @a a full join (select a1,max(b2)as b1 from @b group by a1)b on a.a1=b.a1
result:
a1 b1 b1
----------- ----------- -----------
1 21 31
2 22 34
3 23 NULL

（所影响的行数为 3 行）
------解决方案--------------------
select a.* ,t.b2
from a
full join (select b1,max(b2) as b2 from b group by b1) t
on a.a1=t.b1
------解决方案--------------------
create table A(A1 int, A2 int)
insert A select 1, 21
union all select 2, 22
union all select 3, 23

create table B(A1 int, B3 int)
insert B select 1, 31
union all select 2, 32
union all select 2, 33
union all select 2, 34

select A.*, B.B3 from A
left join
(
select A1, B3=max(B3) from B group by A1
)B on A.A1=B.A1

--result
A1 A2 B3
----------- ----------- -----------
1 21 31
2 22 34
3 23 NULL

(3 row(s) affected)
------解决方案--------------------
表A有字段A1,A2
记录
A1 A2
1 21