试用grouping sets用法

GROUPING SETS 子句是 SELECT 语句的 GROUP BY 子句的扩展。通过 GROUPING SETS 子句，您可采用多种方式对结果分组，而不必使用多个 SELECT 语句来实现这一目的。这就意味着，能够减少响应时间并提高性能。

使用group by进行分组

select deptno,job,count(*),sum(sal),max(hiredate),min(hiredate)
from emp
group by deptno,job 
order by deptno;

结果：
1 10 CLERK 1 1300 1982-1-23 1982-1-23
2 10 MANAGER 1 2450 1981-6-9 1981-6-9
3 10 PRESIDENT 1 5000 1981-11-17 1981-11-17
4 20 ANALYST 2 6000 1987-4-19 1981-12-3
5 20 CLERK 2 1900 1987-5-23 1980-12-17
6 20 MANAGER 1 2975 1981-4-2 1981-4-2
7 30 CLERK 1 950 1981-12-3 1981-12-3
8 30 MANAGER 1 2850 1981-5-1 1981-5-1
9 30 SALESMAN 4 5600 1981-9-28 1981-2-20

使用grouping sets

select deptno,job,count(*),sum(sal),max(hiredate),min(hiredate)
from emp
group by grouping sets((deptno,job),(deptno)) ;

结果：分组小计汇总
1 10 CLERK 1 1300 1982-1-23 1982-1-23
2 10 MANAGER 1 2450 1981-6-9 1981-6-9
3 10 PRESIDENT 1 5000 1981-11-17 1981-11-17
4 10 3 8750 1982-1-23 1981-6-9
5 20 CLERK 2 1900 1987-5-23 1980-12-17
6 20 ANALYST 2 6000 1987-4-19 1981-12-3
7 20 MANAGER 1 2975 1981-4-2 1981-4-2
8 20 5 10875 1987-5-23 1980-12-17
9 30 CLERK 1 950 1981-12-3 1981-12-3
10 30 MANAGER 1 2850 1981-5-1 1981-5-1
11 30 SALESMAN 4 5600 1981-9-28 1981-2-20
12 30 6 9400 1981-12-3 1981-2-20

select deptno,job,count(*),sum(sal),max(hiredate),min(hiredate)
from emp
group by grouping sets((deptno,job),(deptno),()) ;

结果：相比上面多了最后一行总的汇总

1 10 CLERK 1 1300 1982-1-23 1982-1-23
2 10 MANAGER 1 2450 1981-6-9 1981-6-9
3 10 PRESIDENT 1 5000 1981-11-17 1981-11-17
4 10 3 8750 1982-1-23 1981-6-9
5 20 CLERK 2 1900 1987-5-23 1980-12-17
6 20 ANALYST 2 6000 1987-4-19 1981-12-3
7 20 MANAGER 1 2975 1981-4-2 1981-4-2
8 20 5 10875 1987-5-23 1980-12-17
9 30 CLERK 1 950 1981-12-3 1981-12-3
10 30 MANAGER 1 2850 1981-5-1 1981-5-1
11 30 SALESMAN 4 5600 1981-9-28 1981-2-20
12 30 6 9400 1981-12-3 1981-2-20
13 14 29025 1987-5-23 1980-12-17


更深的了解参照http://www.cnblogs.com/caroline/archive/2011/12/10/2283353.html#commentform