Group by的语法

Select [filed1,fild2,]聚合函数（filed）,

[Grouping(filed),]

[Grouping_id(filed1,filed2,…)]

From tablename

Where condition

[Group by {rollup|cube}(filed,filed2)]

[having condition]

[order by filed1]

# 一、基本用法:

（1）我们通过几个例子来研究groupby的基本用法

SQL> create table sales(

2 empid number, --雇员ID

3 depid number, - -部门ID

4 area varchar(20), --区域

5 salenum number); --销售额

SQL> insert into sales values(1,1,'china',10);

SQL> insert into sales values(2,1,'china',10);

SQL> insert into sales values(3,1,'china',10);

SQL> insert into sales values(3,1,'china',10);

SQL> insert into sales values(3,1,'china',10);

SQL> insert into sales values(1,1,'china',10);

SQL> insert into sales values(2,1,'china',10);

SQL> insert into sales values(4,2,'china',10);

SQL> insert into sales values(4,2,'china',10);

SQL> insert into sales values(5,3,'us',10);

SQL> insert into sales values(5,3,'us',10);

SQL> select depid,sum(salenum) from sales group by depid;

DEPID SUM(SALENUM)

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

1        70

2        20

3        20

SQL> select depid,sum(salenum) totalnum from sales

group by depid

having sum(salenum) <30;

DEPID SUM(SALENUM)

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

2        20

3        20

（2）Where 和having的区别

Wheret和having子句都用来筛选数据，但是where是针对原数据进行筛选，而having子句只是针对汇总后的结果进行筛选，所以在需求二的例子中，想要对销售总额进行过滤只能使用having子句

（3）使用order by 排序

SQL> select depid,sum(salenum) from sales group by depid;

DEPID SUM(SALENUM)

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

1       70

2       20

3       20

# 二、扩展用法:

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);

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

### （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）函数

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,…）函数

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

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

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

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