在 SQL 中选择具有多个 GROUP 的表中 TOP 2 值的 SUM

我一直在 SQL Server 2000 中使用集合，并且我的临时表之一 (#Periods) 具有以下表结构：

    RestCTR     HoursCTR    Duration    Rest
    ----------------------------------------
    1           337         2           0
    2           337         46          1
    3           337         2           0
    4           337         46          1
    5           338         1           0
    6           338         46          1
    7           338         2           0
    8           338         46          1
    9           338         1           0
    10          339         46          1
    ...
  

我想做的是计算每个 HoursCTR 的 2 个最长休息时间的总和，最好使用集合和临时表（而不是游标或嵌套子查询）。

这是梦想中的查询，但在 SQL 中不起作用（无论我运行多少次）：

Select HoursCTR, SUM ( TOP 2 Duration ) as LongestBreaks
FROM #Periods
WHERE Rest = 1
Group By HoursCTR    

HoursCTR 可以有任意数量的休息时间（包括没有休息时间）。

我目前的解决方案不是很优雅，基本上涉及以下步骤：

1. 获取最长休息时间，按 HoursCTR 分组
2. 选择第一个（最小）RestCTR 行，该行返回每个 HoursCTR 的最大持续时间
3. 重复步骤 1（排除步骤 2 中已收集的行）
4. 重复步骤 2（再次排除步骤 2 中收集的行）
5. 将 RestCTR 行（来自步骤 2 和 4）合并到单个表中
6. 获取步骤 5 中的行所指向的持续时间的总和，按 HoursCTR 分组

如果有任何设置函数可以减少这个过程，我们将非常欢迎。

在 SQL Server 中执行此操作的最佳方法是使用公用表表达式，使用开窗函数对每组中的行进行编号ROW_NUMBER():

WITH NumberedPeriods AS (
  SELECT HoursCTR, Duration, ROW_NUMBER() 
    OVER (PARTITION BY HoursCTR ORDER BY Duration DESC) AS RN
  FROM #Periods
  WHERE Rest = 1
)
SELECT HoursCTR, SUM(Duration) AS LongestBreaks
FROM NumberedPeriods
WHERE RN <= 2
GROUP BY HoursCTR

edit:我在分区中添加了 ORDER BY 子句，以获得两个最长的休息时间。

抱歉，我没有注意到您需要它才能在 Microsoft SQL Server 2000 中工作。该版本不支持 CTE 或窗口函数。我会留下上面的答案，以防对其他人有帮助。

在 SQL Server 2000 中，常见的建议是使用相关子查询：

SELECT p1.HoursCTR, (SELECT SUM(t.Duration) FROM 
    (SELECT TOP 2 p2.Duration FROM #Periods AS p2
     WHERE p2.HoursCTR = p1.HoursCTR 
     ORDER BY p2.Duration DESC) AS t) AS LongestBreaks
FROM #Periods AS p1