我有一个表,其中包含三个字段,如下所示:
PK account value
-----------------------
1 40010101 130
1 40010101 500
1 40010569 590
2 40010569 300
2 40010101 200
我想为每个PK选择价值最高的记录,按PK和科目分组,因此我的结果应如下所示:
PK account value
-----------------------
1 40010101 630
2 40010569 300
我尝试了以下解决方案:
SELECT
pn.*
FROM
(select PK, account, sum(value) as value
from table
group by PK, account) pn
INNER JOIN
(select PK, MAX(value) AS maxvalue
from (select PK, account, sum(value) as value
from table
group by PK, account) pn
group by PK) maxsum
ON pn.PK= maxsum.PK
AND pn.value = maxsum.maxvalue
它有效,但是我想请您提供一个更快的解决方案.我可以匹配成千上万条记录.我不能使用存储过程.
使用SQL Server.