Group By 多个分组集小结 --GROUPING SETS，GROUP BY CUBE，GROUP BY ROLLUP，GROUPING(),GROUPING_ID()

T-SQL 多个分组集共有三种 GROUPING SETS, CUBE, 以及ROLLUP， 其中 CUBE和ROLLUP可以当做是GROUPING SETS的简写版

示例数据库下载：

https://files.cnblogs.com/files/haseo/TSQL2012.rar

GROUPING SETS

列出所有你设置的分组集

SELECT shipperid, YEAR(shippeddate) AS shipyear, COUNT(*) AS numorders
FROM Sales.Orders
GROUP BY GROUPING SETS
(
( shipperid, YEAR(shippeddate) ),
( shipperid ),
( YEAR(shippeddate) ),
( )
);

CUBE

列出所有可能的分组集

SELECT  shipperid ,
        YEAR(shippeddate) AS shipyear ,
        COUNT(*) AS numorders
FROM    Sales.Orders
GROUP BY CUBE(shipperid, YEAR(shippeddate));

1. ( shipperid, YEAR(shippeddate) )

2. ( shipperid )

3. ( YEAR(shippeddate) )

4. ( )

 

ROOLUP

以层级的方式列出分组集

SELECT  shipcountry ,
        shipregion ,
        shipcity ,
        COUNT(*) AS numorders
FROM    Sales.Orders
GROUP BY ROLLUP(shipcountry, shipregion, shipcity);

1. ( shipcountry, shipregion, shipcity )

2. ( shipcountry, shipregion )

3. ( shipcountry )

4. ( )

 

GROUPING（）
该函数用来区分带入的元素是否属于分组的一部分， 返回0表示属于，1表示不属于

SELECT  shipcountry ,
        GROUPING(shipcountry) AS grpcountry ,
        shipregion ,
        GROUPING(shipregion) AS grpcountry ,
        shipcity ,
        GROUPING(shipcity) AS grpcountry ,
        COUNT(*) AS numorders
FROM    Sales.Orders
GROUP BY ROLLUP(shipcountry, shipregion, shipcity);

 

GROUPING_ID（）

该函数返回分组列的位图（学过二进制的小伙伴都懂的，8421...以此类推） ，如果是0则所有分组字段都是分组的一部分，如果某个分组字段不是分组集的一部分则返回对应数字（既相关二进制位置1），最后进行汇总。

如下代码，如果shipcountry, shipregion, shipcity均是分组的一部分则返回0.  如果shipregion, shipcity 不在分组内，则是3 （0+2+1 ）

SELECT  GROUPING_ID(shipcountry, shipregion, shipcity) AS grp_id ,
        shipcountry ,
        shipregion ,
        shipcity ,
        COUNT(*) AS numorders
FROM    Sales.Orders
GROUP BY ROLLUP(shipcountry, shipregion, shipcity);