若group分组项为XXX,break on XXX skip number,可将分组项只显示为一个名称,其他为空
*注意语法问题:*group by 分组函数若select查询中有col项,则by后必须有相同的col项除聚合函数项除外,
例如:
--sal select语句中出现sal,group up无,错误
select dname,ename,sal from empinfo group by rollup(dname,ename);
-- select语句中出现sal,但为聚合函数,正确
select dname,ename,sum(sal) from empinfo group by rollup(dname,ename);
实例:
--查询同一部门下各种工作的总薪水
SQL> break on deptno skip 2
SQL> select deptno,job,sum(sal) from emp group by rollup(deptno,job);
DEPTNO JOB SUM(SAL)
---------- --------- ----------
10 CLERK 1300
MANAGER 2450
PRESIDENT 5000
8750
20 CLERK 1900
ANALYST 6000
MANAGER 2975
10875
DEPTNO JOB SUM(SAL)
---------- --------- ----------
30 CLERK 950
MANAGER 2850
SALESMAN 5600
9400
29025
已选择13行。
--按部门名称分组
SQL> select dname,ename,sum(sal) from empinfo group by rollup (dname,ename);
DNAME ENAME SUM(SAL)
-------------- ---------- ----------
SALES WARD 1250
SALES ALLEN 1600
SALES BLAKE 2850
SALES JAMES 950
SALES MARTIN 1250
SALES TURNER 1500
SALES 9400
RESEARCH FORD 3000
RESEARCH ADAMS 1100
RESEARCH JONES 2975
RESEARCH SCOTT 3000
DNAME ENAME SUM(SAL)
-------------- ---------- ----------
RESEARCH SMITH 800
RESEARCH 10875
ACCOUNTING KING 5000
ACCOUNTING CLARK 2450
ACCOUNTING MILLER 1300
ACCOUNTING 8750
29025
已选择18行。
SQL> break on dname skip 2
SQL> select dname,ename,sum(sal) from empinfo group by rollup (dname,ename);
DNAME ENAME SUM(SAL)
-------------- ---------- ----------
SALES WARD 1250
ALLEN 1600
BLAKE 2850
JAMES 950
MARTIN 1250
TURNER 1500
9400
RESEARCH FORD 3000
ADAMS 1100
DNAME ENAME SUM(SAL)
-------------- ---------- ----------
RESEARCH JONES 2975
SCOTT 3000
SMITH 800
10875
ACCOUNTING KING 5000
CLARK 2450
MILLER 1300
8750
DNAME ENAME SUM(SAL)
-------------- ---------- ----------
29025