2,596
社区成员
发帖
与我相关
我的任务
分享
create table T( name varchar(100), age int, gangwei int, gongzi int,jiangjin int, heje int)
insert into T select 'A', 30, 1, 400, 100, 500
insert into T select 'B', 31, 1, 800, 210, 1010
insert into T select 'C', 28, 2, 760, 200, 960
insert into T select 'A', 30, 2, 700, 150, 850
insert into T select 'D', 29, 1, 850, 180, 1030
insert into T select 'E', 38, 3, 600, 100, 700
insert into T select 'C', 28, 3, 400, 50, 450
select A.name,A.age,A.gangwei,b.gongzi,b.jiangjin,b.heje
from T as A
inner join
(
select
name,
max(gongzi) as gongzi_Max ,
sum(gongzi) as gongzi,
sum(jiangjin) as jiangjin,
sum(heje) as heje
from T
group by name
) as b on A.name=B.name and a.gongzi=b.gongzi_Max
drop table T