使用匯總通過分組計算總計

我有一個存盤程序：

ALTER PROCEDURE GetReportData
AS
BEGIN
    SELECT 
        LOC.SubCompanyNameVN,
        LOC.BranchName,
        COUNT(LOC.BranchCode) as Total,

        ----------- Not Processed Yet
        SUM (CASE WHEN SS.Status IN (5, 6) THEN 1 ELSE 0 END) AS CountNotProcessedYet,
        
        ----------- Processing
        SUM (CASE WHEN SS.Status IN (3) THEN 1 ELSE 0 END) AS CountProcessing

    FROM 
        DBO.WorkingSession AS SS
    JOIN 
        DBO.Location AS LOC ON SS.LocationID = LOC.LocationID 
                            AND SS.BranchCode = LOC.BranchCode
    JOIN 
        DBO.Status AS ST ON SS.Status = ST.ID
    GROUP BY 
        LOC.SubCompanyNameVN, LOC.BranchName
    ORDER BY 
        LOC.SubCompanyNameVN
END

結果：

但現在我希望我的結果是：

如何按列 SubCompanyNameVN (Group By SubCompanyNameVN) 對結果進行分組以計算如上表所示的總數？我已經研究過，我想我可以用 ROLLUP 解決它，但我對此感到困惑。

uj5u.com熱心網友回復：

除了ROLLUP，您還可以使用GROUPING SETS，這使您可以更靈活地選擇所需的確切匯總。

最好使用GROUPING()函式而不是ISNULL，因為這會告訴您該列是否實際分組，并且您可以看到實際分組之間的差異NULL

 SELECT 
     CASE WHEN GROUPING(LOC.BranchName) = 0
       THEN LOC.BranchName
       ELSE LOC.SubCompanyNameVN
       END AS SubCompanyOrBranchName,
     COUNT(LOC.BranchCode) as Total,

     ----------- Not Processed Yet
     SUM (CASE WHEN SS.Status IN (5, 6) THEN 1 ELSE 0 END) AS CountNotProcessedYet,
     
     ----------- Processing
     SUM (CASE WHEN SS.Status IN (3) THEN 1 ELSE 0 END) AS CountProcessing

 FROM 
     DBO.WorkingSession AS SS
 JOIN 
     DBO.Location AS LOC ON SS.LocationID = LOC.LocationID 
                         AND SS.BranchCode = LOC.BranchCode
 JOIN 
     DBO.Status AS ST ON SS.Status = ST.ID
 GROUP BY GROUPING SETS (
     (LOC.SubCompanyNameVN, LOC.BranchName),
     (LOC.SubCompanyNameVN)
 )
 ORDER BY 
     LOC.SubCompanyNameVN,
     GROUPING(LOC.BranchName) DESC, -- put the totalled rows first
     LOC.BranchName;

uj5u.com熱心網友回復：

使用cte并union all與group by

With cte AS ( 
     SELECT 
        LOC.SubCompanyNameVN,
        LOC.BranchName,
        COUNT(LOC.BranchCode) as Total,

        ----------- Not Processed Yet
        SUM (CASE WHEN SS.Status IN (5, 6) THEN 1 ELSE 0 END) AS CountNotProcessedYet,
        
        ----------- Processing
        SUM (CASE WHEN SS.Status IN (3) THEN 1 ELSE 0 END) AS CountProcessing,
        ROW_NUMBER() OVER(PARTITION BY LOC.SubCompanyNameVN ORDER BY LOC.SubCompanyNameVN) AS seq

    FROM 
        DBO.WorkingSession AS SS
    JOIN 
        DBO.Location AS LOC ON SS.LocationID = LOC.LocationID 
                            AND SS.BranchCode = LOC.BranchCode
    JOIN 
        DBO.Status AS ST ON SS.Status = ST.ID
    GROUP BY 
        LOC.SubCompanyNameVN, LOC.BranchName)
        
select SubCompanyNameVN,Total,CountNotProcessedYet,CountProcessing
from
(select SubCompanyNameVN,
       sum(Total) As Total,
       sum(CountNotProcessedYet) As CountNotProcessedYet,
       sum(CountProcessing) As CountProcessing,
       dense_rank()over(order by SubCompanyNameVN) As rnk
   from cte
   group by SubCompanyNameVN
   union all
   select BranchName,
       Total,
       CountNotProcessedYet,
       CountProcessing,
       dense_rank()over(order by SubCompanyNameVN) As rnk
   from cte) T
order by rnk,SubCompanyNameVN desc

db<>fiddle 中的演示

uj5u.com熱心網友回復：

我最終通過使用找到了解決方案ROLLUP，這是我需要的：

SELECT 
        (CASE 
            WHEN
                BranchName is NULL
                    THEN SubCompanyNameVN ELSE BranchName
        END) AS SubCompanyOrBranchName,
        Total, CountNotProcessedYet, PercentNotProcessedYet, CountProcessing, PercentProcessing,
        CountProcessedIn5Days, PercentProcessedIn5Days, CountProcessedOver5Days, PercentProcessedOver5Days
    FROM
    (
        SELECT 
            LOC.SubCompanyNameVN,
            LOC.BranchName,
            COUNT(LOC.BranchCode) as Total,
            ----------- Not Processed Yet
            SUM (CASE WHEN SS.Status IN (5, 6) THEN 1 ELSE 0 END) AS CountNotProcessedYet,
        
            ----------- Processing
            SUM (CASE WHEN SS.Status IN (3) THEN 1 ELSE 0 END) AS CountProcessing

        FROM DBO.WorkingSession AS SS
        JOIN DBO.Location AS LOC ON SS.LocationID = LOC.LocationID AND SS.BranchCode = LOC.BranchCode
        JOIN DBO.Status AS ST ON SS.Status = ST.ID
        GROUP BY ROLLUP(LOC.SubCompanyNameVN, LOC.BranchName)
        ORDER BY LOC.SubCompanyNameVN, LOC.BranchName OFFSET 1 ROWS
    ) T

標籤：sql sql-server 查询语句 通过...分组 卷起

上一篇：使用輸入引數從包中呼叫所需的程序

下一篇：我應該使用哪種語言來存盤測驗資料？