有一表：table
cpspcode cpscode ipsquantity
A1 E 20.000000
A1 F 36.000000
A1 D 4.000000
A2 E 50.000000
A2 F 72.000000
A2 D 7.000000
现想把它显示成：（第一个字的取值是由第三个字段决定。取第三个字段最大值对应的打一个字段的值）
A2 D 11
A2 E 70
A2 F 108
------解决方案--------------------
----创建测试数据
declare @t table(cpspcode varchar(10),cpscode varchar(10),ipsquantity int)
insert @t
select 'A1 ', 'E ', 20.000000 union all
select 'A1 ', 'F ', 36.000000 union all
select 'A1 ', 'D ', 4.000000 union all
select 'A2 ', 'E ', 50.000000 union all
select 'A2 ', 'F ', 72.000000 union all
select 'A2 ', 'D ', 7.000000
----查询
SELECT cpspcode = (select top 1 cpspcode from @t where cpscode = a.cpscode ORDER BY ipsquantity DESC),
a.cpscode,sum(a.ipsquantity) as ipsquantity
FROM @t as a GROUP BY a.cpscode
/*结果
cpspcode cpscode ipsquantity
---------- ---------- -----------
A2 D 11
A2 E 70
A2 F 108
*/