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);