总结一下用sql的cube和rollup实现汇总小计

总结一下用sql的cube和rollup实现汇总小计

9534发表于2015-10-28

做数据统计时,经常会有汇总小计的需求,要实现这样的需求有很多种方案,可以在用sql、也可以用DataTable在C#代码里实现汇总。本文介绍一下用sql实现方法。具我所知,sql里面要实现汇总小计可以用compute,也可以group by xx cube和roll。

二者的区别:

是compute生成的结果是多个结果集,而cube和roll是生成一个,而且cube和rollup效率更高,更直观。

sql server官方也推荐用cube和rollup,因为微软官方文档中说compute在sql server的以后的版本会舍弃掉。

下面通过一个具体的例子来说明一下用sql的cube和rollup实现汇总小计方法和cube和rollup的生成的结果区别。表结构和数据如下图:

1、一个字段分组

统计相同年龄的用户数
1、使用ROLLUP
SELECT p.age,COUNT(1) AS Cnt FROM Person p
GROUP BY p.age with ROLLUP
2、使用CUBE
SELECT p.age,COUNT(1) as Cnt FROM Person p
GROUP BY p.age with CUBE

当Group By只有一个字段时,ROLLUP和CUBE的结果是一样的,上面两个sql执行结果都是:

所以:当Group By只有一个字段时,ROLLUP和CUBE是没有区别的。

2、多个字段分组

统计同一个地址同一个年龄的用户数
1、ROLLUP
SELECT p.address,p.age,COUNT(1) AS Cnt FROM Person p
GROUP BY p.address,p.age with ROLLUP
结果:
本例可以看出用ROLLUP会增加第一个分组字段的汇总小计和两个分组的总计(最后一行)。
注意:使用with rollup比没用使用的group by 多了第一个分组字段的汇总和两个分组汇总; 顺序是从左到右。
有下面的两种组合:
address,null
null,null
2、CUBE
SELECT p.address,p.age,COUNT(1) as Cnt FROM Person p
GROUP BY p.address,p.age with CUBE
结果:
从结果中可以看出用CUBE生成的汇总小计的行数要比使用ROLLUP更多,而且排序也不一样了。
本例可以看出用CUBE会增加最后一个分组字段的汇总小计,两个分组的总计和第一个分组字段的汇总。
注意:使用with CUBE比没用使用的group by 多了第一个分组字段的汇总和两个分组汇总,加上第一个分组字段的汇总; 顺序是从右到左。
有下面的三种组合:
null,age
null,null
address,null

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)这样汇总小计行都在最后了,显得更加直观了。

小编蓝狐