SQL> --
where和having均可使用时，尽量采用where！ 因为where可以先对数据进行过滤然后分组，提高分组效率！
SQL> host cls
group by的增强 SQL> /*
SQL> group by的增强
SQL> group by deptno,job
SQL> +
SQL> group by deptno
SQL> +
SQL> group by null
SQL> =
SQL> group by rollup(deptno,job)
SQL> SQL>
SQL> group by rollup(a,b)
SQL> =
SQL> group by a,b
SQL> +
SQL> group by a
SQL> +
SQL> group by null
SQL> */ SQL> select deptno,job,sum(sal)
2 from emp
3 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
30 9400
29025
已选择13行。
设定格式 SQL> break on deptno skip 2
SQL> /
DEPTNO JOB SUM(SAL)
---------- --------- ----------
10 CLERK 1300
MANAGER 2450
PRESIDENT 5000
8750
20 CLERK 1900
ANALYST 6000
MANAGER 2975
10875
30 CLERK 950
MANAGER 2850
SALESMAN 5600
9400
DEPTNO JOB SUM(SAL)
---------- --------- ----------
29025
已选择13行。
取消格式 SQL> break on null
SQL> /
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
30 9400
29025
已选择13行。
SQL> spool off
|