SQL*Plus break与compute的简单用法(三)
ed.
--基于多列的break
--下面的查询中除了基于deptno分组之外，还增加了基于job进行分组
goex_admin@SYBO2SZ> break on deptno on job skip 1;
goex_admin@SYBO2SZ> select * from emp order by deptno,job;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
7934 MILLER CLERK 7782 19820123 00:00:00 1400 10
7782 CLARK MANAGER 7839 19810609 00:00:00 2550
7839 KING PRESIDENT 19811117 00:00:00 5100
7788 SCOTT ANALYST 7566 19870419 00:00:00 3100 20
7902 FORD 7566 19811203 00:00:00 3100
7876 ADAMS CLERK 7788 19870523 00:00:00 1200
7369 SMITH 7902 19801217 00:00:00 900
7566 JONES MANAGER 7839 19810402 00:00:00 3075
7900 JAMES CLERK 7698 19811203 00:00:00 1050 30
7698 BLAKE MANAGER 7839 19810501 00:00:00 2950
7654 MARTIN SALESMAN 7698 19810928 00:00:00 1350 1400
7521 WARD 7698 19810222 00:00:00 1350 500
7499 ALLEN 7698 19810220 00:00:00 1700 300
7844 TURNER 7698 19810908 00:00:00 1600 0
14 rows selected.
2、compute的用法
[sql]
a、获取帮助信息
goex_admin@SYBO2SZ> help compute
COMPUTE
-------
In combination with the BREAK command, calculates and prints
summary lines using various standard computations. Also lists
all COMPUTE definitions.
COMP[UTE] [function [LAB[EL] text] ...
OF {expr|column|alias} ...
ON {expr|column|alias|REPORT|ROW} ...]
b、命令特性描述
compute用于分组值计算指定的列上的数值，实际上等同于对分组列执行group by，然后调用聚合函数。
function为常用的聚合函数，如sum,avg,maximum,minimum,std,count等等。
of为指定的计算列，也就是说要计算哪一列。
on为分组条件，基于哪个列，表达式，report，row等进行分组。
compute通常结合break来用，否则相当于没有分组，聚合也就没有任何意义。
c、演示compute用法
goex_admin@SYBO2SZ> set pagesize 80
goex_admin@SYBO2SZ> clear break -->清除break的设置
breaks cleared
goex_admin@SYBO2SZ> break on deptno skip 1
goex_admin@SYBO2SZ> compute sum of sal on deptno -->基于deptno对sal求和
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
---------- **********
11375 sum