group by rollup & grouping

-------------所用表-----------------
SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10

----------------group by -----------------

SQL> select ename,sal
2 from emp
3 group by ename
4 order by ename;
select ename,sal
*
第 1 行出现错误:
ORA-00979: 不是 GROUP BY 表达式


SQL> select ename,sal
2 from emp
3 group by ename,sal
4 order by ename;

ENAME SAL
---------- ----------
ADAMS 1100
ALLEN 1600
BLAKE 2850
CLARK 2450
FORD 3000
JAMES 950
JONES 2975
KING 5000
MARTIN 1250
MILLER 1300
SCOTT 3000
SMITH 800
TURNER 1500
WARD 1250

-----------group by rollup ---------------------

SQL> select ename,sum(sal)
2 from emp
3 group by rollup ename
4 order by ename;
group by rollup ename
*
第 3 行出现错误:
ORA-00933: SQL 命令未正确结束

SQL> select ename,sal
2 from emp
3 group by rollup (ename)
4 order by ename;
select ename,sal
*
第 1 行出现错误:
ORA-00979: 不是 GROUP BY 表达式

SQL> select ename,sum(sal)
2 from emp
3 group by rollup(ename)
4 order by ename;

ENAME SUM(SAL)
---------- ----------
ADAMS 1100
ALLEN 1600
BLAKE 2850
CLARK 2450
FORD 3000
JAMES 950
JONES 2975
KING 5000
MARTIN 1250
MILLER 1300
SCOTT 3000
SMITH 800
TURNER 1500
WARD 1250
29025

已选择15行。

SQL> select ename,sal
2 from emp
3 group by rollup (ename,sal)
4 order by ename;

ENAME SAL
---------- ----------
ADAMS 1100
ADAMS
ALLEN 1600
ALLEN
BLAKE 2850
BLAKE
CLARK 2450
CLARK
FORD 3000
FORD
JAMES 950
JAMES
JONES 2975
JONES
KING 5000
KING
MARTIN 1250
MARTIN
MILLER 1300
MILLER
SCOTT 3000
SCOTT
SMITH 800
SMITH
TURNER 1500
TURNER
WARD 1250
WARD


已选择29行。

---------------- group by rollup --------------
SQL> select grouping(ename),ename,sal*12
2 from emp
3 group by rollup(ename)
4 order by ename;
select grouping(ename),ename,sal*12
*
第 1 行出现错误:
ORA-00979: 不是 GROUP BY 表达式

SQL> select grouping(ename),ename,sum(sal)
2 from emp
3 group by rollup(ename)
4 order by ename;

GROUPING(ENAME) ENAME SUM(SAL)
--------------- ---------- ----------
0 ADAMS 1100
0 ALLEN 1600
0 BLAKE 2850
0 CLARK 2450
0 FORD 3000
0 JAMES 950
0 JONES 2975
0 KING 5000
0 MARTIN 1250
0 MILLER 1300
0 SCOTT 3000
0 SMITH 800
0 TURNER 1500
0 WARD 1250
1 29025

已选择15行。

SQL> select grouping(ename),ename,sal*12
2 from emp
3 group by rollup(ename,sal)
4 order by ename;

GROUPING(ENAME) ENAME SAL*12
--------------- ---------- ----------
0 ADAMS 13200
0 ADAMS
0 ALLEN 19200
0 ALLEN
0 BLAKE 34200
0 BLAKE
0 CLARK 29400
0 CLARK
0 FORD 36000
0 FORD
0 JAMES 11400
0 JAMES
0 JONES 35700
0 JONES
0 KING 60000
0 KING
0 MARTIN 15000
0 MARTIN
0 MILLER 15600
0 MILLER
0 SCOTT 36000
0 SCOTT
0 SMITH 9600
0 SMITH
0 TURNER 18000
0 TURNER
0 WARD 15000
0 WARD
1

已选择29行。



























[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/14181270/viewspace-1058735/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/14181270/viewspace-1058735/