sql-server - 最大值之和(TOP x)

Question

假设我有一个如下表:

PK Code Value
1  A    200
2  A    300
3  A    25
4  A    75
5  A    50
6  A    15
7  A    300
8  A    75

如何获取 code=A 的前 4 个最高值的值(即只需要 300 + 300 + 200 + 75 的总和)

谢谢

Answer 1

您可以使用派生表或通用表表达式来获取前 4 个，然后对其进行SUM。

SELECT SUM(Value) As Top4Sum
FROM
(
    SELECT TOP (4) Value
    FROM YourTable
    WHERE Code = 'A'
    ORDER BY Value DESC
) T

如果您想要每个 Code 的 TOP 4 的 SUM，您可以这样做

;WITH CTE
     AS (SELECT *,
                ROW_NUMBER() OVER (PARTITION BY Code ORDER BY Value DESC) RN
         FROM   YourTable)
SELECT Code,
       SUM(Value)
FROM   CTE
WHERE  RN <= 4
GROUP  BY Code