分组函数（2）

 

前面介绍了分组函数的基本用法，下面介绍几种关于GROUP BY更高级的用法。

1、ORACLE数据库中的ROLLUP配合GROUP BY命令使用，可以提供信息汇总功能（与"小计"相似）

SQL> SELECT deptno, job,sum(sal) from emp1 group by deptno,job

2  order by deptno,job;

DEPTNO JOB                           SUM(SAL)

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

10 CLERK                             1300

10 MANAGER                           2450

10 PRESIDENT                         5000

20 ANALYST                           6000

20 CLERK                             1900

20 MANAGER                           2975

30 CLERK                              950

30 MANAGER                           2850

30 SALESMAN                          5600

40 SALESMAN                          6000

//会对每一个不同的dept,job生成一行独立的结果

1  SELECT deptno, job,sum(sal) from emp1

2  group by rollup(deptno,job)

3* order by deptno,job

SQL> /

DEPTNO JOB                           SUM(SAL)

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

10 CLERK                             1300

10 MANAGER                           2450

10 PRESIDENT                         5000

10                                   8750

20 ANALYST                           6000

20 CLERK                             1900

20 MANAGER                           2975

20                                  10875

30 CLERK                              950

30 MANAGER                           2850

30 SALESMAN                          5600

30                                   9400

40 SALESMAN                          6000

40                                   6000

35025

//结果中除了上述的结果结果之外，还会对每一个deptno进行一个小结，并单独生成一行，除此之外还会对所有的sal求和并生成一行。

总结：

（1）可以看出,用了rollup的group by子句所产生的所谓的超级聚合就是指在在产生聚合时会从右向左逐个对每一列进行小结，并在结果中生成独立的一行，同时也会对聚合列生成一个合计列。

（2）这里的group by后面我们仅仅接了2列，实际上我们可以使用更多列的，这样的话oracle就会以从右向左的方式来进行逐个小结。

2、CUBE，也是GROUP BY子句的一种扩展

可以返回每一个列组合的小计记录（从左向右+从右向左），同时在末尾加上总计记录。

1  SELECT deptno, job,sum(sal) from emp1

2  group by cube(deptno,job)

3* order by deptno,job

SQL> /

DEPTNO JOB                           SUM(SAL)

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

10 CLERK                             1300

10 MANAGER                           2450

10 PRESIDENT                         5000

10                                   8750

20 ANALYST                           6000

20 CLERK                             1900

20 MANAGER                           2975

20                                  10875

30 CLERK                              950

30 MANAGER                           2850

30 SALESMAN                          5600

30                                   9400

40 SALESMAN                          6000

40                                   6000

ANALYST                           6000

CLERK                             4150

MANAGER                           8275

PRESIDENT                         5000

SALESMAN                         11600

35025

这里需要注意的是是使用了group by和rollup后，其后面的列要用括号括起来，否则将会出现ORA-00933: SQL 命令未正确结束的错误。

3、其他情况

但是我们大多数情况下需要在查询的结果集的汇总列加上“合计”，怎么办呢？用grouping和grouping_id函数，然后再用decode函数判断一下是否为空就可以了。

select decode(grouping_id(job,deptno),1,'sum',job||deptno) as group_col,sum(sal) total_sal

from emp

group by rollup(job,deptno);

GROUP_COL           TOTAL_SAL

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

CLERK10                  1300

CLERK20                  1900

CLERK30                   950

sum                      4150

ANALYST20                6000

sum                      6000

MANAGER10                2450

MANAGER20                2975

MANAGER30                2850

sum                      8275

SALESMAN30               5600

sum                      5600

PRESIDENT10              5000

sum                      5000

29025

GROUPING函数可以接受一列，返回0或者1。如果列值为空，那么GROUPING()返回1；如果列值非空，那么返回0。GROUPING只能在使用ROLLUP或CUBE的查询中使用。当需要在返回空值的地方显示某个值时，GROUPING()就非常有用。