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

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

示例数据库下载:

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);
上一篇:图解SQL的Join 转自coolshell


下一篇:Ubuntu如何选择更新源