这个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有主键没,没的话加个主键ID
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