GROUP BY 的基本用法和扩展用法（主要是扩展用法）

一、基本用法

GROUP BY 是分组查询, 一般 GROUP BY 是和 聚合函数配合使用,你可以想想

你用了GROUP BY 按  ITEM.ITEMNUM 这个字段分组,那其他字段内容不同,变成一对多又改如何显示呢,比如下面所示

A  B
1  abc
1  bcd
1  asdfg

select A,B from table group by A
你说这样查出来是什么结果,

A  B
   abc
1  bcd
   asdfg

右边3条如何变成一条,所以需要用到聚合函数,比如

select A,count(B) 数量 from table group by A
这样的结果就是
A  数量
1   3

二、扩展用法:

扩展用法使用下面的表进行实验研究

SQL> create table testgroup(

2 a varchar(5),

3 b varchar(5),

4 c varchar(5),

5 n number);

建完测试表,然后插入两条测试数据

SQL> insert into testgroup values('a1','b1','c1',10);

SQL> insert into testgroup values('a1','b1','c1',20);

我们使用基本的group by 可以得到以下结果

SQL> select a,b,c,sum(n) total from testgroup group by a,b,c;

A        B      C     TOTAL

----- ----- ----- ----------

a1      b1     c1     30

（1）使用rollup操作符

Rollup意思有”卷起,汇总”的意思,他可以在使得在其括号中的字段,按从右到左的顺序分别group后显示,类似我们用多个group by 语句,然后union all起来,我们把针对上面的测试表,使用rollup操作符,看看效果

SQL> select a,b,c,sum(n) total from testgroup group by rollup(a,b,c);

Result:

A	B	C	TOTAL
a1	b1	c1	30
a1	b1		30
a1			30
			30

从上面结果可以看出, 除了对(a1,b1,c1)进行了汇总外,又从右向左分别对子句中的”a,b,c”字段进行了汇总,例如(a1,b1),(a1) ()

（2）使用cube操作符

Cube意思是立方,使用该操作符可以对操作符内的字段,进行遍历组合汇总,例如cube(a,b,c),那么就会产生8种组合结果,分别如下”a-b-c”,”a-b”,”a”,”a-c”,” b-c”,”b”,”c”,”空”,看下面的例子

SQL> select a,b,c,sum(n) total from testgroup group by cube(a,b,c);

        Result:

A	B	C	TOTAL
			30
		c1	30
	b1		30
	b1	c1	30
a1			30
a1		c1	30
a1	b1		30
a1	b1	c1	30

（3）,使用grouping（filed）函数

使用grouping 函数必须先理解rollup 和cube操作符,那么grouping函数有什么用呢?在日常应用中,我们通过rollup或者cube对汇总进行了汇总,汇总后的结果往往要传送到应用程序端,在应用程序端我们必须要有一个依据来判断某行数据是不是按照rollup或cube进行汇总,grouping函数可以用来产生这个依据,他可以接收一个参数,判断该参数是否为null,是则返回1,否则返回0,我样可以据此来判断该是否按某列进行汇总统计的,当然在实验应用中,0和1看起来不那么直观,我们可以使用decode或者case函数进行转议,让查看结果看起来更直观,请看以下例子

SQL> select grouping(a) ca,grouping(b) cb,grouping(c) cc, a,b,c,sum(n) from testgroup group by rollup(a,b,c);

Result:

CA	CB	CC	A	B	C	SUM(N)
0	0	0	a1	b1	c1	30
0	0	1	a1	b1		30
0	1	1	a1			30
1	1	1				30

（4）使用grouping_id（filed1,file2,…）函数

使用grouping函数有时候感觉不是那么灵活的，他只能接收一个字段，而grouping_id()函数则可以接收多个字段，GROUPING_ID()函数可以接受一列或多列，返回按GROUPING位向量进行计算的十进制值。下面我们通过一个例子来研究grouping_id函数是如何按照grouping函数的位向量进行计算的。

SQL> select grouping(a) ca,grouping(b) cb,grouping_id(a,b) caandb, a,b,sum(n) from testgroup group by rollup(a,b);

Result:

CA	CB	CAANDB	A	B	SUM(N)
0	0	0	a1	b1	30
0	1	1	a1		30
1	1	3			30

位向量计算方法：如上例，

第一行，CA=0,CB=0，那么位向量就是“00”，换算成十进制是0

第二行，CA=0,CB=1，那么位向量就是‘01’，换算成十进制是1

第三行，CA=1,CB=1，那么位向量就是‘11’，换算成十进制是3

请注意上例中grouping_id的计算值跟括号内的字段顺序有关，上例中书写顺序是grouping_id(a,b),a字段在前面，如果换下顺序grouping_id(b,a)，计算结果是不一样的，看下例

SQL> select grouping(a) ca,grouping(b) cb,grouping_id(b,a) caandb, a,b,sum(n) from testgroup group by rollup(a,b);

Result:

CA	CB	CAANDB	A	B	SUM(N)
0	0	0	a1	b1	30
0	1	2	a1		30
1	1	3			30

看看第二行红色字体，grouping_id中的字段顺序发生了变化，位向量值也不一样了

(4-1) grouping_id（）函数的用途

上面讲了grouping_id的用法，但在日常工作中，我们如何应用该函数呢？其实只要了解了他的原理及用法，要怎么用，就看我们是否可以灵活使用了，下面介绍一种常见的用法：

假如我们要对某testgroup进行分组统计，并且过滤掉不包括小计或总计的行，这时grouping_id就有用武之地了，我们可以利用grouping_id的值结合having子句，通过判断grouping_id是否大于0来过滤掉不需要的行。

SQL> select grouping(a) ca,grouping(b) cb,grouping_id(a,b) caandb, a,b,sum(n) from testgroup group by rollup(a,b) having grouping_id(a,b)>0;

Result:

CA	CB	CAANDB	A	B	SUM(N)
0	1	1	a1		30
1	1	3			30