SQL Server ->> GROUPING SETS, CUBE, ROLLUP, GROUPING, GROUPING_ID

上面代码输出的结果

通常GROUPING SETS会配合GROUPING_ID或GROUPING函数来完成列的输出，比如聚合列的标签内容。

GROUPING_ID (a, b, c)  = GROUPING(a) + GROUPING(b) + GROUPING(c)

GROUPING 用于区分标准空值和由 ROLLUP、CUBE 或 GROUPING SETS 返回的空值。 作为 ROLLUP、CUBE 或 GROUPING SETS 操作结果返回的 NULL 是 NULL 的特殊应用。 它在结果集内作为列的占位符，表示全体。其实意思就是如果列输出为NULL说明是聚合列，不为空就不是聚合列。一开始很难理解。

SELECT 
    T.[Group]
    ,T.CountryRegionCode
    ,S.Name AS N‘Store‘
    ,(SELECT P.FirstName + ‘ ‘ + P.LastName 
        FROM Person.Person AS P 
        WHERE P.BusinessEntityID = H.SalesPersonID)
        AS N‘Sales Person‘
    ,SUM(TotalDue)AS N‘TotalSold‘
    ,CAST(GROUPING(T.[Group])AS char(1)) + 
        CAST(GROUPING(T.CountryRegionCode)AS char(1)) + 
        CAST(GROUPING(S.Name)AS char(1)) + 
        CAST(GROUPING(H.SalesPersonID)AS char(1)) 
        AS N‘GROUPING base-2‘
    ,GROUPING_ID((T.[Group])
        ,(T.CountryRegionCode),(S.Name),(H.SalesPersonID)
        ) AS N‘GROUPING_ID‘
    ,CASE 
        WHEN GROUPING_ID(
            (T.[Group]),(T.CountryRegionCode)
            ,(S.Name),(H.SalesPersonID)
            ) = 15 THEN N‘Grand Total‘
        WHEN GROUPING_ID(
            (T.[Group]),(T.CountryRegionCode)
            ,(S.Name),(H.SalesPersonID)
            ) = 14 THEN N‘SalesPerson Total‘
        WHEN GROUPING_ID(
            (T.[Group]),(T.CountryRegionCode)
            ,(S.Name),(H.SalesPersonID)
            ) = 13 THEN N‘Store Total‘
        WHEN GROUPING_ID(
            (T.[Group]),(T.CountryRegionCode)
            ,(S.Name),(H.SalesPersonID)
            ) = 12 THEN N‘Store SalesPerson Total‘
        WHEN GROUPING_ID(
            (T.[Group]),(T.CountryRegionCode)
            ,(S.Name),(H.SalesPersonID)
            ) = 11 THEN N‘CountryRegionCode Total‘
        WHEN GROUPING_ID(
            (T.[Group]),(T.CountryRegionCode)
            ,(S.Name),(H.SalesPersonID)
            ) =  7 THEN N‘Group Total‘
        ELSE N‘Error‘
        END AS N‘Level‘
FROM Sales.Customer AS C
    INNER JOIN Sales.Store AS S
        ON C.StoreID  = S.BusinessEntityID 
    INNER JOIN Sales.SalesTerritory AS T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader AS H
        ON C.CustomerID = H.CustomerID
GROUP BY GROUPING SETS ((S.Name,H.SalesPersonID)
    ,(H.SalesPersonID),(S.Name)
    ,(T.[Group]),(T.CountryRegionCode),()
    )
--HAVING GROUPING_ID(
--    (T.[Group]),(T.CountryRegionCode),(S.Name),(H.SalesPersonID)
--    ) = @GroupingLevel
ORDER BY 
    GROUPING_ID(S.Name,H.SalesPersonID),GROUPING_ID((T.[Group])
    ,(T.CountryRegionCode)
    ,(S.Name)
    ,(H.SalesPersonID))ASC;

上面代码来自MSDN的例子，数据库是示例数据库--AdventureWork2012

在只有一列作为GROUP BY CUBE/ROLLUP的列的情况下，两个函数是一样的效果。

CUBE和ROLLUP的区别就是CUBE比ROLLUP多输出多列的情况下，CUBE比ROLLUP多做的事情就是针对后面列的单独聚合行输出。

WITH T AS (
SELECT ‘A‘ A, ‘A‘ B UNION ALL
SELECT ‘A‘ A, ‘B‘ B UNION ALL 
SELECT ‘A‘ A, ‘C‘ B UNION ALL
SELECT ‘B‘ A, ‘A‘ B UNION ALL
SELECT ‘B‘ A, ‘B‘ B UNION ALL
SELECT ‘B‘ A, ‘C‘ B UNION ALL
SELECT ‘C‘ A, ‘A‘ B UNION ALL
SELECT ‘C‘ A, ‘B‘ B UNION ALL
SELECT ‘C‘ A, ‘C‘ B)

SELECT  A, B, 
        COUNT(A) AS CNT,
        GROUPING_ID(A,B)
FROM T
GROUP BY CUBE(A, B);


WITH T AS (
SELECT ‘A‘ A, ‘A‘ B UNION ALL
SELECT ‘A‘ A, ‘B‘ B UNION ALL 
SELECT ‘A‘ A, ‘C‘ B UNION ALL
SELECT ‘B‘ A, ‘A‘ B UNION ALL
SELECT ‘B‘ A, ‘B‘ B UNION ALL
SELECT ‘B‘ A, ‘C‘ B UNION ALL
SELECT ‘C‘ A, ‘A‘ B UNION ALL
SELECT ‘C‘ A, ‘B‘ B UNION ALL
SELECT ‘C‘ A, ‘C‘ B)

SELECT  A, B, 
        COUNT(A) AS CNT,
        GROUPING_ID(A,B)
FROM T
GROUP BY ROLLUP(A, B);

SQL Server ->> GROUPING SETS, CUBE, ROLLUP, GROUPING, GROUPING_ID

标签：