相信很多朋友都经常会使用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)