Oracle命令 CUBE
Oracle命令 CUBE
Oracle基本命令: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)是不一样的。
|
评论暂时关闭