这个SQL语句 如何写

[cjwcyc]

表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

这个查询如体写???

[caixia615]

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

[mugua604]

--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

[caixia615]

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 行）

[playwarcraft]

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

[marco08]

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)

[Well]

表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))


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

[rookie_one]

select t1.*,t2.B3
from A t1 left join (Select A1,MAX(B3) as B3 from B group by A1) t2 on t1.A1=t2.A1

[Well]

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

[DragonBill]

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

[DragonBill]

想不到楼上的占先了

[baggio328]

一群牛人都来了
...

[jackyqk]

晕。。。都这么多人写了啊。。。LZ的100分果然吸引人。。。

[mincunbao]

来个笨的：
declare @A table(A1 int primary key,A2 int)
insert @A select '1','21'
union all select '2','22'
union all select '3','23'

declare @B table (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.A1,A.A2,Max(B.B3) B3 from @A A left join @B B
on A.A1=B.A1 group by A.A1,A.A2



(3 行受影响)

(4 行受影响)
A1 A2 B3
----------- ----------- -----------
1 21 31
2 22 34
3 23 NULL
(3 行受影响)

[cjwcyc]

如果表A 中还其字段A3,A4,如果表B 中还其字段B3,B4
如何写？？？？

[marco08]

思路基本一樣的

[y_dong119]

天道的效率比较不错,而且比较简单,容易看懂,楼主可以优先选择哈

[ziyuan_1016]

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

我试了，结果是
A1 A2 A1 B3
----------- ----------- ----------- ---------
1 21 1 31
2 22 2 34
3 23 NULL NULL


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

[xiequanqin]

大家的思路都差不多~