这个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)
--结果
12131
22234
323NULL
[解决办法]
--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
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
[解决办法]
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
[解决办法]
select * from A
left join (select A1,max(B2) B2 from B group by A1) C
on A.A1=C.A1
[解决办法]
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
[解决办法]
晕。。。都这么多人写了啊。。。LZ的100分果然吸引人。。。
[解决办法]
来个笨的：
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 行受影响)
[解决办法]
思路基本一樣的
[解决办法]
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
