请教中！请好心人帮忙
有一表：table
cpspcode cpscode ipsquantity
A1 E 20.000000
A1 F 86.000000
A1 D 4.000000
A2 E 50.000000
A2 F 72.000000
A2 D 7.000000
现想把它显示成：（第一个字的取值是由第三个字段决定,第一个字的值是第三个字最大的值的所对应的第一个字段的值）
A2 D 11
A2 E 70
A1 F 158
谢谢！
[解决办法]
没注意,楼主的测试数据改动了,这样看起来就明显了:
----创建测试数据
declare @t table(cpspcode varchar(10),cpscode varchar(10),ipsquantity int)
insert @t
select 'A1 ', 'E ', 20.000000 union all
select 'A1 ', 'F ', 86.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
A1 F 158
*/
