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

相信很多朋友都经常会使用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 byall safetystocklevel
查询结果：

可以看到统计结果多出了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的情形。