基本命令:GROUP BY CUBE(字段列表);
CUBE命令根据字段列表,生成不同的排列组合,并根据每一种组合结果,生成统计汇总。
比如:GROUP BY CUBE( city, job, age );排列组合的结果如示:
1)city,job,age
2)city, job
3)city
4)job, age
5)age
6)city , age
7) job
所以就有7种排列组合。
2)根据每一种排列组合,生成不同的统计汇总,如下:
1) group by cube(city, job ,age );
2) group by cube(city, job);
3) group by cube(city);
4)group by cube(job, age );
5)group by cube(age);
6)group by cube(city, age);
7)group by cube(job);
举例说明:
1)先统观我们要用到的表(TABLE ):
SQL> SELECT * FROM EMP ;
EMPNO ENAME JOB MGR HIREDATE SAL DEPTNO
---------- ---------- --------- ----- ----------- --------- ------
7369 SMITH CLERK 7902 1980/12/17 800.00 20
7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 30
7521 WARD SALESMAN 7698 1981/2/22 1250.00 30
7566 JONES MANAGER 7839 1981/4/2 2975.00 20
7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 30
7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30
7782 CLARK MANAGER 7839 1981/6/9 2450.00 10
7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20
7839 KING PRESIDENT 1981/11/17 5000.00 10
7844 TURNER SALESMAN 7698 1981/9/8 1500.00 30
7876 ADAMS CLERK 7788 1987/5/23 1100.00 20
7900 JAMES CLERK 7698 1981/12/3 950.00 30
7902 FORD ANALYST 7566 1981/12/3 3000.00 20
7934 MILLER CLERK 7782 1982/1/23 1300.00 10
2) 根据职业(JOB)求不同职业的平均薪水(SAL):
SQL> SELECT JOB, AVG(SAL) FROM EMP GROUP BY JOB ORDER BY JOB ASC ;
JOB AVG(SAL)
--------- ----------
ANALYST 3000
CLERK 1037.5
MANAGER 2758.33333
PRESIDENT 5000
SALESMAN 1400
3)根据不同部门(DEPTNO),不同职业(JOB),求平均薪水(SAL):
SQL> SELECT JOB, DEPTNO , AVG(SAL) FROM EMP GROUP BY JOB , DEPTNO ORDER BY JOB;
JOB DEPTNO AVG(SAL)
--------- ------ ----------
ANALYST 20 3000
CLERK 10 1300
CLERK 20 950
CLERK 30 950
MANAGER 10 2450
MANAGER 20 2975
MANAGER 30 2850
PRESIDENT 10 5000
SALESMAN 30 1400
-- 可以看到不同部门(DEPTNO:10,20,30)的平均年薪水AVG(SAL)是不一样的。
4)统计汇总:
SQL> SELECT JOB, DEPTNO, AVG(SAL) FROM EMP GROUP BY CUBE(JOB, DEPTNO) ORDER BY JOB DESC ;
JOB DEPTNO AVG(SAL)
--------- ------ ----------
10 2916.66666
20 2175
30 1566.66666
2073.21428
SALESMAN 30 1400
SALESMAN 1400
PRESIDENT 10 5000
PRESIDENT 5000
MANAGER 10 2450
MANAGER 20 2975
MANAGER 30 2850
MANAGER 2758.33333
CLERK 10 1300
CLERK 20 950
CLERK 30 950
CLERK 1037.5
ANALYST 20 3000
ANALYST 3000
---18 rows selected 。。///共有18行结果
我们来看看:group by cube(JOB,DEPTNO) order by JOB desc ; 根据JOB ,DEPTNO进性统计汇总,排列组合如下:1)JOB,DEPTNO
2) JOB
3)DEPTNO
4)去掉JOB和DEPTNO
统计汇总如下:
1)根据JOB,DEPTNO 统计汇总,结果如下:
SALESMAN 30 1400
PRESIDENT 10 5000
MANAGER 10 2450
MANAGER 20 2975
MANAGER 30 2850
CLERK 10 1300
CLERK 20 950
CLERK 30 950
ANALYST 20 3000
--9 rows selected。。//9行结果。
2)根据JOB统计汇总,结果如下:
SALESMAN 1400
PRESIDENT 5000
MANAGER 2758.33333
CLERK 1037.5
ANALYST 3000
。。//共5行被选取。
3)根据DEPTNO统计汇总结果如下:
10 2916.66666
20 2175
30 1566.66666
。。//共3行被选取。
4)剩下最后一行:
2073.21428
。。//剩下最后1行,
为了验证这个结果是否准确,测试下:
SQL> SELECT AVG(SAL) FROM EMP; AVG(SAL) ---------- 2073.21428
--//累计9+5+3+1=18与 SELECT JOB, DEPTNO, AVG(SAL) FROM EMP GROUP BY CUBE(JOB, DEPTNO) ORDER BY JOB DESC ; 查询结果相同
GROUP BY CUBE( );通过举例说明,应该还是很容易理解的。