要使用CUBE,首先要了解GROUP BY。
其实CUBE和ROLLUP区别不太大,只是在基于GROUP BY 子句创建和汇总分组的可能的组合上有一定差别,CUBE将返回的更多的可能组合。如果在GROUP BY子句中有N个列或者是有N个表达式的话,SQLSERVER在结果集上会返回2的N-1次幂个可能组合。
CUBE和ROLLUP之间的区别在于:
CUBE 生成的结果集显示了所选列中值的所有组合的聚合。
ROLLUP生成的结果集显示了所选列中值的某一层次结构的聚合
ROLLUP就是将GROUP BY后面的第一列名称求总和,而其他列并不要求
而CUBE则会将每一个列名称都求总和
COPY了一个例子,首先用ROLLUP
查询语句: Select cust_id,product_code,sum(qty) as quantity From invoices Where cust_id IN (4,5) Group By cust_id, product_code WITH Rollup Order By cust_id 查询结果: cust_id product_code quantity ------- -------------- ------- NULL NULL 10 4 5 3 4 6 3 4 NULL 6 5 5 4 5 NULL 4 第一行是4,5买的所有产品的数量( 第二行是4买5产品的数量 第三行是4买6产品的数量 第四行是4买所有产品的数量(按照cust_id=4总汇) 第五行是5买5产品的数量 第六行是5买所有产品的数量(按照cust_id=5总汇) 如果查询语句中的ROLLUP关键字更改为CUBE,就会多出有关产品的信息 查询结果: cust_id product_code quantity ------- -------------- ------- NULL NULL 10 NULL 5 7 NULL 6 3 4 5 3 4 6 3 4 NULL 6 5 5 4 5 NULL 4 第2行是所有顾客买5产品的数量(product_code=5 总汇) 第3行是所有顾客买6产品的数量(product_code=6 总汇)附:其他解释Oracle ROLLUP和CUBE 用法Oracle的GROUP BY语句除了最基本的语法外,还支持ROLLUP和CUBE语句。
如果是Group by ROLLUP(A, B, C)的话,首先会对(A、B、C)进行GROUP BY,然后对(A、B)进行GROUP BY,然后是(A)进行GROUP BY,最后对全表进行GROUP BY操作。
如果是GROUP BY CUBE(A, B, C),则首先会对(A、B、C)进行GROUP BY,然后依次是(A、B),(A、C),(A),(B、C),(B),(C),最后对全表进行GROUP BY操作。 grouping_id()可以美化效果。除了使用GROUPING函数,还可以使用GROUPING_ID来标识GROUP BY的结果。
也可以 Group by Rollup(A,(B,C)) ,Group by A Rollup(B,C),…… 这样任意按自己想要的形式结合统计数据,非常方便。
3、使用grouping美化结果
在我们使用了cube和rollup的的group by语句的结果中可以看到有些列是null,这些是小结生成了,我们可以使用grouping函数把这这些替换成有意义的文字,比如小计、合计。grouping函数官方是这样说明的:
GROUPING:
是一个聚合函数,它产生一个附加的列,当用 CUBE 或 ROLLUP 运算符添加行时,附加的列输出值为1,当所添加的行不是由 CUBE 或 ROLLUP 产生时,附加列值为0。 仅在与包含 CUBE 或 ROLLUP 运算符的 GROUP BY 子句相联系的选择列表中才允许分组。
语法:GROUPING ( column_name )
参数:column_name是 GROUP BY 子句中用于检查 CUBE 或 ROLLUP 空值的列。
返回类型:int
好了,有了上面的知识我们把这前写的第一个sql语句改下:
- SELECT case when grouping(p.age)=1 THEN '总计' ELSE cast(p.age as varchar(50)) END age,COUNT(1) AS Cnt
- FROM Person p
- GROUP BY p.age with ROLLUP
执行结果:
两个多个字段的group by 稍微复杂一些。
- SELECT case when grouping(p.address)=1 THEN '总计' ELSE p.[address] END address
- ,case when grouping(p.age)=1 AND grouping(p.[address])<>1 THEN '小计' ELSE cast(p.age as varchar(50)) END age
- ,COUNT(1) AS Cnt
- FROM Person p
- GROUP BY p.address,p.age with ROLLUP
- ORDER BY GROUPING(p.[address]),GROUPING(p.age)
执行结果:
上面加了一个排序:ORDER BY GROUPING(p.[address]),GROUPING(p.age)这样汇总小计行都在最后了,显得更加直观了。
注:grouping用法的原博文地址:点击打开链接