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

做数据统计时，经常会有汇总小计的需求，要实现这样的需求有很多种方案，可以在用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)这样汇总小计行都在最后了，显得更加直观了。