Group by all | with cube | with rollup 的用法

相信很多朋友都经常会使用Group By来进行数据的统计，本文介绍可能大家比较少用，但实际上有时候又很有用的三个Group By查询。

1. group by all

SQL Server联机丛书中是这样描述group by all的：“只有在 SELECT 语句还包括 WHERE 子句时，ALL 关键字才有意义。”也就是，group by all必须和where子句一起使用。

我们使用SQL Server 2005自带的AdventureWorks数据库的Production.Product表进行演示。

首先看不使用all关键字的情形：

select safetystocklevel,count(productid) as productcount from Production.Product where finishedgoodsflag=1 group by safetystocklevel
查询结果：

可以看到，我们按safetystocklevel对产品进行了分类统计，显示了符合条件（finishedgoodsflag=1）的统计结果。

然后我们加上all关键字

select safetystocklevel,count(productid) as productcount from Production.Product where finishedgoodsflag=1 group by allsafetystocklevel
查询结果：

可以看到统计结果多出了3行，并且这3行的productcount值都为0。由此我们可以看到加上all关键字的作用，就是将不符合where子句的那部分数据也给显示出来，也就是说显示safetystocklevel的所有可能情况，对不符合finishedgoodsflag=1的则显示为0或者NULL。

2.group by ... with cube

group by ... with cube或with rollup只有在有两个以上的分类字段的情况下才有用。

我们先看没有with cube的情形：

select makeflag,finishedgoodsflag,count(productid) from Production.Product group by finishedgoodsflag,makeflag

查询结果：

可以看到分别显示了makeflag为0或1，finishedgoodsflag为0或1所产生的4种组合的统计结果。

然后加上with cube

select makeflag,finishedgoodsflag,count(productid) from Production.Product group by finishedgoodsflag,makeflag with cube

可以看到，出了原来的4组统计数据外，还多了几行，我们逐行来看多出来的几行：

NULL  0  209  ——统计finishedgoodsflag为0的记录数，不管makeflag，makeflag可以为0或1，也就是182+27

NULL  1  259 ——统计finishedgoodsflag为0的记录数，不管makeflag，makeflag可以为0或1，也就是83+212

NULL  NULL  504 ——不管finishedgoodsflag和makeflag的取值，也就是所有的记录的总数

0  NULL  265 ——统计makeflag为0的记录数

1  NULL  239 ——统计makeflag为1的记录数

还有一个要注意的，就是查询得出来的结果的排序，由于我们使用的是group by finishedgoodsflag,makeflag，所以它先统计完finishedgoodsflag，接着是全部记录数，最后才统计makeflag。

2.group by ... with rollup

这个跟group by ... with cube的作用类似，只不过统计的情形会少一些，我们来看一下：

select makeflag,finishedgoodsflag,count(productid) from Production.Product group by finishedgoodsflag,makeflag with rollup

查询结果

可以看到少了 0 NULL和 1 NULL的组合，也就是说，它只会考虑finishedgoodsflag（在group by中排在前面）的统计情形，以及全部记录的计数，而不会单独考虑makeflag的情形。

group by后使用rollup子句总结

一、如何理解group by后带rollup子句所产生的效果

group by后带rollup子句的功能可以理解为：先按一定的规则产生多种分组，然后按各种分组统计数据（至于统计出的数据是求和还是最大值还是平均值等这就取决于SELECT后的聚合函数）。因此要搞懂group by后带rollup子句的用法主要是搞懂它是如何按一定的规则产生多种分组的。另group by后带rollup子句所返回的结果集，可以理解为各个分组所产生的结果集的并集且没有去掉重复数据。下面举例说明：

1、对比没有带rollup的goup by

例：Group by A ,B

产生的分组种数：1种；

即group by A,B

返回结果集：也就是这一种分组的结果集。

2、带rollup但group by与rollup之间没有任何内容

例1：Group byrollup(A ,B)

产生的分组种数：3种；

第一种：group by A,B

第二种：group by A

第三种：group by NULL

（说明：本没有group by NULL 的写法，在这里指是为了方便说明，而采用之。含义是：没有分组，也就是所有数据做一个统计。例如聚合函数是SUM的话，那就是对所有满足条件的数据进行求和。此写法的含义下同)

返回结果集：为以上三种分组统计结果集的并集且未去掉重复数据。

例2：Group byrollup(A ,B,C)

产生的分组种数：4种；

第一种：group by A,B,C

第二种：group by A,B

第三种：group by A

第四种：group by NULL

返回结果集：为以上四种分组统计结果集的并集且未去掉重复数据。

3、带rollup但group by与rollup之间还包含有列信息

例1：Group byA , rollup(A ,B)

产生的分组种数：3种；

第一种：group by A,A,B 等价于group by A,B

第二种：group by A,A 等价于group by A

第三种：group by A,NULL 等价于group by A

返回结果集：为以上三种分组统计结果集的并集且未去掉重复数据。

例2：Group byC , rollup(A ,B)

产生的分组种数：3种；

第一种：group by C,A,B

第二种：group by C,A

第三种：group by C,NULL 等价于group by C

返回结果集：为以上三种分组统计结果集的并集且未去掉重复数据。

4、带rollup且rollup子句括号内又使用括号对列进行组合

例1：Group byrollup((A ,B))

产生的分组种数：2种；

第一种：group by A,B

第二种：group by NULL

返回结果集：为以上两种分组统计结果集的并集且未去掉重复数据。

例2：Group byrollup(A ,(B,C))

产生的分组种数：3种；

第一种：group by A,B,C

第二种：group by A

第三种：group by NULL

返回结果集：为以上三种分组统计结果集的并集且未去掉重复数据。

注：对这种情况，可以理解为几个列被括号括在一起时，就只能被看成一个整体，分组时不需要再细化。因此也可推断rollup括号内也顶多加到一重括号，加多重了应该没有任何意义（这个推断我没有做验证的哦）。

二、与rollup组合使用的其它几个辅助函数

1、grouping()函数

必须接受一列且只能接受一列做为其参数。参数列值为空返回1，参数列值非空返回0。

2、grouping_id()函数

必须接受一列或多列做为其参数。

返回值为按参数排列顺序，依次对各个参数使用grouping()函数，并将结果值依次串成一串二进制数然后再转化为十进制所得到的值。

例如：grouping(A) = 0 ;grouping(B) = 1;

则：grouping_id(A,B) = (01)2 = 1;

grouping_id(B,A) =(10)2 =2;

3、group_id()函数

调用时不需要且不能传入任何参数。

返回值为某个特定的分组出现的重复次数(第一大点中的第3种情况中往往会产生重复的分组)。重复次数从0开始，例如某个分组第一次出现则返回值为0，第二次出现时返回值为1，……，第n次出现返回值为n-1。

注：使用以上三个函数往往是为了过滤掉一部分统计数据，而达到美化统计结果的作用。

三、group by后带rollup子句与group by后带cube子句区别

group by后带rollup子句与group by后带cube子句的唯一区别就是：

带cube子句的group by会产生更多的分组统计数据。cube后的列有多少种组合（注意组合是与顺序无关的）就会有多少种分组。

例：Group by cube(A ,B,C)

产生的分组种数：8种；

第一种：group by A,B,C

第二种：group by A,B

第三种：group by A,C

第四种：group by B,C

第五种：group by C

第六种：group by B

第七种：group by A

第八种：group by NULL

返回结果集：为以上八种分组统计结果集的并集且未去掉重复数据。

四、group by后带grouping sets子句

group by后带grouping sets子句效果就是只返回小记记录，即只返回按单个列分组后的统计数据，不返回多个列组合分组的统计数据。

例1：Group bygrouping sets(A )

产生的分组种数：1种；

第一种：group by A

返回结果集：即为以上一种分组的统计结果集。

例2：Group bygrouping sets(A ,B)

产生的分组种数：2种；

第一种：group by A

第二种：group by B

返回结果集：为以上两种分组统计结果集的并集且未去掉重复数据。

例3：Group bygrouping sets (A ,B,C)

产生的分组种数：3种；

第一种：group by A

第二种：group by B

第三种：group by C

返回结果集：为以上三种分组统计结果集的并集且未去掉重复数据。

select t.city_name,
decode(t.directline_type,
null,
'总数',
directline_type,
directline_type),
sum(t.amount)
from tmp_dl_info t
group by rollup(t.city_name, t.directline_type)