問題描述

我試圖將每個案例的行數(shù)限制為 5 行.有些案例只有 1 或 2 行，但有些案例有 15 行或更多.

I am trying to limit the number of rows per case to only 5 rows. Some cases have only 1 or 2 rows but some have 15 or more.

這是我用來計算每個案例的行數(shù)的存儲過程示例.

This is an example of a stored procedure that I am using to count the number of rows per 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)

這是這個存儲過程產(chǎn)生的截圖:screenshot

Here is a screenshot of what this stored procedure produces: screenshot

我嘗試使用 where 子句，但它不允許我在 where 子句之后放置窗口函數(shù).它也無法識別我的Row"別名.

I have tried to use the where clause but it will not allow me to place a window function after the where clause. It also does not recognize my "Row" alias.

是否有另一種方法來計算每個案例的數(shù)量或行數(shù)(而不是窗口函數(shù))，以便我可以使用 where 子句?或者有沒有辦法使用我現(xiàn)有的存儲過程將每個案例的記錄限制為 5 個?

Is there another way to count the number or rows per case (instead of a window function) so that I can use the where clause? Or is there a way to limit the records per case to 5 using my existing stored procedure?

預(yù)先感謝您的幫助.

推薦答案

您可以將語句包裝在 CTE 中，因為 SQL Server 支持它.

You can wrap your statement in a CTE since SQL Server supports it.

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

這篇關(guān)于限制每個 ID 的行數(shù)的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！