This website requires JavaScript.

MSSQL:Group By 多个分组集小结

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

GROUPING SETS,GROUP BY CUBE,GROUP BY ROLLUP,GROUPING(),GROUPING_ID()

示例数据库下载:

http://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);

 
0条评论
avatar