做数据统计时,经常会有汇总小计的需求,要实现这样的需求有很多种方案,可以在用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的生成的结果区别。表结构和数据如下图:
![](http://www.lanhusoft.com/attached/image/20150209/20150209231110_6526.gif)
1、一个字段分组
- SELECT p.age,COUNT(1) AS Cnt FROM Person p
- GROUP BY p.age with ROLLUP
- 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、多个字段分组
- SELECT p.address,p.age,COUNT(1) AS Cnt FROM Person p
- GROUP BY p.address,p.age with ROLLUP
![](http://www.lanhusoft.com/attached/image/20150209/20150209231222_3245.gif)
- SELECT p.address,p.age,COUNT(1) as Cnt FROM Person p
- GROUP BY p.address,p.age with CUBE
![](http://www.lanhusoft.com/attached/image/20150209/20150209231245_4182.gif)
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
执行结果:
![](http://www.lanhusoft.com/attached/image/20150209/20150209231315_6213.gif)
- 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)这样汇总小计行都在最后了,显得更加直观了。
转载请注明:文章转载自:蓝狐软件工作室 » 总结一下用sql的cube和rollup实现汇总小计