SQL*Plus break与compute的简单用法(五)
7698 19811203 00:00:00 1050 30
7698 BLAKE MANAGER 7839 19810501 00:00:00 2950 30
7654 MARTIN SALESMAN 7698 19810928 00:00:00 1350 1400 30
----------
avg 2173.21429
sum 30425
14 rows selected.
goex_admin@SYBO2SZ> compute sum avg of sal on deptno -->对deptno分组进行求和，同时求平均
goex_admin@SYBO2SZ> break on deptno skip 1;
goex_admin@SYBO2SZ> select * from emp order by deptno;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 19810609 00:00:00 2550 10
7839 KING PRESIDENT 19811117 00:00:00 5100
7934 MILLER CLERK 7782 19820123 00:00:00 1400
---------- **********
3016.66667 avg
9050 sum
7566 JONES MANAGER 7839 19810402 00:00:00 3075 20
7902 FORD ANALYST 7566 19811203 00:00:00 3100
7876 ADAMS CLERK 7788 19870523 00:00:00 1200
7369 SMITH CLERK 7902 19801217 00:00:00 900
7788 SCOTT ANALYST 7566 19870419 00:00:00 3100
---------- **********
2275 avg
11375 sum
7521 WARD SALESMAN 7698 19810222 00:00:00 1350 500 30
7844 TURNER SALESMAN 7698 19810908 00:00:00 1600 0
7499 ALLEN SALESMAN 7698 19810220 00:00:00 1700 300
7900 JAMES CLERK 7698 19811203 00:00:00 1050
7698 BLAKE MANAGER 7839 19810501 00:00:00 2950
7654 MARTIN SALESMAN 7698 19810928 00:00:00 1350 1400
---------- **********
1666.66667 avg
10000 sum
14 rows selected.
goex_admin@SYBO2SZ> break on deptno skip 1;
goex_admin@SYBO2SZ> compute sum of sal comm on deptno --对sal以及comm基于分组deptno同时求和
goex_admin@SYBO2SZ> select * from emp order by deptno;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 19810609 00:00:00 2550 10
7839 KING PRESIDENT 19811117 00:00:00 5100
7934 MILLER CLERK 7782 19820123 00:00:00 1400
---------- ---------- **********
9050 sum
7566 JONES MANAGER 7839 19810402 00:00:00 3075 20
7902 FORD ANALYST 7566 19811203 00:00:00 3100
7876 ADAMS CLERK 7788 19870523 00:00:00 1200
7369 SMITH CLERK 7902 19801217 00:00:00 900
7788 SCOTT ANALYST 7566 19870419 00:00:00 3100