ROLLUP 与 CUBE 运算符的使用

    对于数据的汇总，是数据库经常用到的任务之一，除了我们通常使用的GROUP BY分组配合聚合函数对数据汇总，以及使用UNION ALL 对数据汇总之外，SQL还提供了 GROUP BY Col1,Col2.. WITH CUBE | ROLLUP，以及COMPUTE BY 等汇总方式，本文主要介绍了使用CUBE 与ROLLUP运算符来实现数据的分级汇总。

IF OBJECT_ID('tb') IS NOT NULL  DROP TABLE tb; GO   CREATE TABLE tb (     Provider    VARCHAR(10)     ,MaterialNo VARCHAR(3)     ,Quantity INT );  INSERT INTO tb SELECT 'Canon',  '001',  500  UNION ALL SELECT 'Canon',  '001',     200  UNION ALL SELECT 'Canon',  '002',     100 UNION ALL SELECT 'Canon',  '002',     300 UNION ALL SELECT 'Sony',  '001',     200  UNION ALL SELECT 'IBM',  '002',     100  UNION ALL SELECT 'IBM',  '001',     600  UNION ALL SELECT 'IBM',  '001',     200  

--1.使用ROLLUP来对数据进行汇总 SELECT Provider,MaterialNo,SUM(Quantity) AS Sum_Quantity FROM tb GROUP BY Provider,MaterialNo WITH ROLLUP /*  --结果 Provider   MaterialNo Sum_Quantity ---------- ---------- ------------ Canon      001        700 Canon      002        400 Canon      NULL       1100 IBM        001        800 IBM        002        100 IBM        NULL       900 Sony       001        200 Sony       NULL       200 NULL       NULL       2200 */ 由结果可以得知，ROLLUP运算符实现了对Provider级别进行汇总，结果集中Provider字段不为空，MaterialNo字段为空的时候实现了对不同的Provider分类汇总，而当Provider与MaterialNo都为空时则是对所有的Provider来实现数据汇总。

--2.使用CUBE来对数据进行汇总 SELECT Provider,MaterialNo,SUM(Quantity) AS Sum_Quantity FROM tb GROUP BY Provider,MaterialNo WITH CUBE /*  --结果 Provider   MaterialNo Sum_Quantity ---------- ---------- ------------ Canon      001        700 Canon      002        400 Canon      NULL       1100 IBM        001        800 IBM        002        100 IBM        NULL       900 Sony       001        200 Sony       NULL       200 NULL       NULL       2200 NULL       001        1700 NULL       002        500 */ 与ROLLUP相比，从结果可以看出CUBE的结果集在ROLLUP结果集的基础上增加了两行， NULL       001        1700 NULL       002        200 即对不同MaterialNo也实现了汇总。

--3.ROLLUP与CUBE的差异  . ROLLUP 生成的结果集为所选列中值的某一维度的聚合。如以上示例中实现了对Provider维度进行汇总。  . CUBE  生成的结果集为所选列中值的所有维度的聚合。如以上示例中实现了对Provider和MaterialNo所有维度进行汇总。 --4.使用GROUPING函数来处理汇总产生的NULL值      对于使用ROLLUP与CUBE汇总数据所产生的NULL值，容易引起与实际数据本身为NULL容易引起歧义，对此我们可以使用GROUPING函数的进行区分。      当NULL为ROLLUP或CUBE所产生时，则GROUPING函数返回的值为1，当NULL来自实际数据本身的话，GROUPING函数返回的值为0。 SELECT     CASE WHEN (GROUPING(Provider) = 1) THEN 'ALL'          ELSE Provider END AS Provider,     CASE WHEN (GROUPING(MaterialNo) = 1) THEN 'ALL'          ELSE MaterialNo END AS MaterialNo,     SUM(Quantity) AS Sum_Quantity FROM tb GROUP BY Provider,MaterialNo WITH CUBE  /*  --结果 Provider   MaterialNo Sum_Quantity ---------- ---------- ------------ Canon      001        700 Canon      002        400 Canon      ALL        1100 IBM        001        800 IBM        002        100 IBM        ALL        900 Sony       001        200 Sony       ALL        200 ALL        ALL        2200 ALL        001        1700 ALL        002        500 */

--5.使用Having子句来顾虑掉无用的数据，比如本例过滤掉了总计以及MaterialNo汇总的数据。 SELECT     CASE WHEN (GROUPING(Provider) = 1) THEN 'ALL'          ELSE Provider END AS Provider,     CASE WHEN (GROUPING(MaterialNo) = 1) THEN 'ALL'          ELSE MaterialNo END AS MaterialNo,     SUM(Quantity) AS Sum_Quantity FROM tb GROUP BY Provider,MaterialNo WITH CUBE HAVING GROUPING(Provider) <> 1 /*  --结果 Provider   MaterialNo Sum_Quantity ---------- ---------- ------------ Canon      001        700 Canon      002        400 Canon      ALL        1100 IBM        001        800 IBM        002        100 IBM        ALL        900 Sony       001        200 Sony       ALL        200 */

--过滤掉了相同Provider，不同的MaterialNo 的小计数据和最终的总计数据 SELECT     CASE WHEN (GROUPING(Provider) = 1) THEN 'ALL'          ELSE Provider END AS Provider,     CASE WHEN (GROUPING(MaterialNo) = 1) THEN 'ALL'          ELSE MaterialNo END AS MaterialNo,     SUM(Quantity) AS Sum_Quantity FROM tb GROUP BY Provider,MaterialNo WITH CUBE HAVING GROUPING(MaterialNo) = 0

总结:CUBE，ROLLUP为多维数据集的汇总提供了可能，当需要对所有维度进行汇总，应当使用CUBE运算符，对某一维度进行汇总则使用ROLLUP运算法。      需要注意的是，WITH CUBE | ROLLUP必须跟在GROUP BY Col1,Col2列之后，然后可以通过使用HAVING子句配合GROUPING函数来过滤不需要的结果集。