|
20 ANALYST 6000
CLERK 1900
MANAGER 2975
10875
DEPTNO JOB SUM(SAL)
------ --------- ----------
30 CLERK 950
MANAGER 2850
SALESMAN 5600
9400
29025
已选择13行。
SQL> break on null
SQL> select deptno,job,sum(sal) from emp group by rollup(deptno,job);
DEPTNO JOB SUM(SAL)
------ --------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 8750
20 CLERK 1900
20 ANALYST 6000
20 MANAGER 2975
20 10875
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
DEPTNO JOB SUM(SAL)
------ --------- ----------
30 9400
29025
已选择13行。
SQL> --打开sql执行时间
SQL> set timing on
SQL> select deptno,job,sum(sal) from emp group by rollup(deptno,job);
DEPTNO JOB SUM(SAL)
------ --------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 8750
20 CLERK 1900
20 ANALYST 6000
20 MANAGER 2975
20 10875
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
DEPTNO JOB SUM(SAL)
------ --------- ----------
30 9400
29025
已选择13行。
已用时间: 00: 00: 00.00
SQL> select deptno,job,sum(sal) from emp group by deptno,job
2 union
3 select deptno,to_char(null),sum(sal) from emp group by deptno
4 union
5 select to_number(null),to_char(null),sum(sal) from emp;
DEPTNO JOB SUM(SAL)
------ --------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 8750
20 ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
20 10875
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
DEPTNO JOB SUM(SAL)
------ --------- ----------
30 9400
29025
已选择13行。
已用时间: 00: 00: 00.01
SQL> set timing off
SQL> select ename,sal from emp
2 where sal between 700 and 1300
3 INTERSECT
4 select ename,sal from emp
5 where sal between 1201 and 1400;
ENAME SAL
---------- -----
MARTIN 1250
MILLER 1300
WARD 1250
SQL> select ename,sal from emp
2 where sal between 700 and 1300
3 minus
4 select ename,sal from emp
5 where sal between 1201 and 1400;
ENAME SAL
---------- -----
ADAMS 1100
JAMES 950
SMITH 800
SQL> spool off
|