请教中！谢谢
有一表：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
*/