请教中！请好心人帮忙
有一表：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
*/