限制每个ID的行数

Question

我正在尝试将每个案例的行数限制为只有5行。有些情况下只有1到2行，但有些情况下有15行或更多行。

这是一个存储过程的示例，我使用它来计算每个case的行数。

SELECT     ROW_NUMBER() OVER(partition by rce.reportruncaseid ORDER BY rce.Reportruncaseid) AS Row, rce.ReportRunCaseId AS CaseId, YEAR(rce.EcoDate) AS EcoYear
FROM         PhdRpt.ReportCaseList AS rcl INNER JOIN
                  PhdRpt.RptCaseEco AS rce ON rce.ReportId = rcl.ReportId AND rce.ReportRunCaseId = rcl.ReportRunCaseId
GROUP BY rce.ReportId, rce.ReportRunCaseId, YEAR(rce.EcoDate)
Order by rce.ReportRunCaseId, YEAR(rce.EcoDate)

下面是这个存储过程所产生的屏幕截图：screenshot

我曾尝试使用where子句，但它不允许我在where子句之后放置窗口函数。它也不能识别我的"Row“别名。

有没有其他方法可以计算每种情况的行数(而不是窗口函数)，这样我就可以使用where子句了？或者，有没有办法使用我现有的存储过程将每个案例的记录限制为5条？

提前感谢您的帮助。

Answer 1

您可以将语句包装在CTE中，因为SQL Server支持它。

WITH records
AS
(
    SELECT  ROW_NUMBER() OVER(PARTITION BY rce.reportruncaseid 
                              ORDER BY rce.Reportruncaseid) AS Row, 
            rce.ReportRunCaseId AS CaseId, 
            YEAR(rce.EcoDate) AS EcoYear
    FROM    PhdRpt.ReportCaseList AS rcl 
                INNER JOIN PhdRpt.RptCaseEco AS rce 
                    ON  rce.ReportId = rcl.ReportId 
                        AND rce.ReportRunCaseId = rcl.ReportRunCaseId
    GROUP   BY rce.ReportId, rce.ReportRunCaseId, YEAR(rce.EcoDate)
)
SELECT CaseId, EcoYear
FROM   records
WHERE  row <= 10
ORDER  BY CaseId, EcoYear