SQL*Plus break与compute的简单用法

   在SQL*Plus提示符下输出求和报表,我们可以借助break与compute两个命令来实现。这个两个命令简单易用,可满足日常需求,其实质也相当于在编写SQL语句时使用分组及聚合函数。不同的是在报表中的分组的最下方或整个报表的最下方我们可以得到如sum,avg以及自定义的聚合字样。见下面的演示。

1、break的用法

a、获取帮助信息 
--如果帮助不可用,需要安装SQL*Plus help,参考: SQL*PLus 帮助手册(SP2-0171) http://blog.csdn.net/robinson_0612/article/details/8852568 
goex_admin@SYBO2SZ> help break

 BREAK
 -----

 Specifies where changes occur in a report and the formatting
 action to perform, such as:
 - suppressing display of duplicate values for a given column
 - skipping a line each time a given column value changes
   (In iSQL*Plus, only when Preformatted Output is ON)
 - printing computed figures each time a given column value
   changes or at the end of the report.
 Enter BREAK with no clauses to list the current BREAK definition.

 BRE[AK] [ON report_element [action [action]]] ...

 where report_element has the following syntax:
     {column | expression | ROW | REPORT}

 and where action has the following syntax:
     [SKI[P] n | [SKI[P]] PAGE] [NODUP[LICATES] | DUP[LICATES]]

 The SKIP option is not supported in iSQL*Plus

b、命令特性描述
break 命令主要用于过滤重复列,正如单词所表达的意思及中断,也就是说中断显示重复的列。
当下一行记录的上指定的列与上一行相同,不显示该列,否则显示该列。
当使用break时,通常建议sql语句使用Order by 子句。可以基于order by子句使用多个列,同样break 也可以使用多个列。
report_element表明可以基于列,表达式,行,以及report等多种不同类型来进行中断显示,也就是说break on对哪个进行分组。
action则表示
     skip[n],在每个分组的最后,自动跳过n个空行。
     skip page, 在每个分组的最后,自动换页。
     break on row skip[n],每一行后面跳过n个空行。
     nodup 重复的显示空,dup重复的也显示。

c、演示break用法
--基于列deptno进行中断显示
goex_admin@SYBO2SZ> break on deptno
goex_admin@SYBO2SZ> break     --break用于查看当前break的设置信息
break on deptno nodup

--下面查询中,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
      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
      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

14 rows selected.

--指定skip参数为n,则每一个新的分组之后插入指定的行数,如下面的查询为1,则新分组后插入1空行
goex_admin@SYBO2SZ> break on deptno skip 1
goex_admin@SYBO2SZ> break
break on deptno skip 1 nodup

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

      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

      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


14 rows selected.
--上面的查询结果中分组deptno 30之后也被跳过一行,所以显示的结果尾部与"14 rows selected"有两行间隙

--下面基于row来分组,且插入1空行
goex_admin@SYBO2SZ> break on row skip 1;
goex_admin@SYBO2SZ> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON

--此时设置pagesize为8
goex_admin@SYBO2SZ> set pagesize 8
goex_admin@SYBO2SZ> break on deptno skip page   -->基于页面进行跳页
--下面的查询基于deptno被分为了4个页面
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

     EMPNO ENAME      JOB              MGR HIREDATE                 SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
      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

     EMPNO ENAME      JOB              MGR HIREDATE                 SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
      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

     EMPNO ENAME      JOB              MGR HIREDATE                 SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
      7654 MARTIN     SALESMAN        7698 19810928 00:00:00       1350       1400         30

14 rows selected.

--基于多列的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的用法

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来用,否则相当于没有分组,聚合也就没有任何意义。

--Author : Robinson
--Blog   : http://blog.csdn.net/robinson_0612

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

      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
                                                             ----------            **********
                                                                  10000            sum

goex_admin@SYBO2SZ> break on report skip 1                    
goex_admin@SYBO2SZ> compute sum of sal on report   -->对整个report的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                    10
      7934 MILLER     CLERK           7782 19820123 00:00:00       1400                    10
      7566 JONES      MANAGER         7839 19810402 00:00:00       3075                    20
      7902 FORD       ANALYST         7566 19811203 00:00:00       3100                    20
      7876 ADAMS      CLERK           7788 19870523 00:00:00       1200                    20
      7369 SMITH      CLERK           7902 19801217 00:00:00        900                    20
      7788 SCOTT      ANALYST         7566 19870419 00:00:00       3100                    20
      7521 WARD       SALESMAN        7698 19810222 00:00:00       1350        500         30
      7844 TURNER     SALESMAN        7698 19810908 00:00:00       1600          0         30
      7499 ALLEN      SALESMAN        7698 19810220 00:00:00       1700        300         30
      7900 JAMES      CLERK           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
                                                             ----------
sum                                                               30425


14 rows selected.                                              

goex_admin@SYBO2SZ> compute sum avg of sal on report   -->对整个report求和以及求平均
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                    10
      7934 MILLER     CLERK           7782 19820123 00:00:00       1400                    10
      7566 JONES      MANAGER         7839 19810402 00:00:00       3075                    20
      7902 FORD       ANALYST         7566 19811203 00:00:00       3100                    20
      7876 ADAMS      CLERK           7788 19870523 00:00:00       1200                    20
      7369 SMITH      CLERK           7902 19801217 00:00:00        900                    20
      7788 SCOTT      ANALYST         7566 19870419 00:00:00       3100                    20
      7521 WARD       SALESMAN        7698 19810222 00:00:00       1350        500         30
      7844 TURNER     SALESMAN        7698 19810908 00:00:00       1600          0         30
      7499 ALLEN      SALESMAN        7698 19810220 00:00:00       1700        300         30
      7900 JAMES      CLERK           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
                                                             ---------- ---------- **********
                                                                  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
                                                             ---------- ---------- **********
                                                                  10000       2200 sum

goex_admin@SYBO2SZ> compute avg of sal on deptno;   --对sal列基于分组deptno求平均
goex_admin@SYBO2SZ> compute sum of comm on deptno;  --对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
                                                             ---------- ---------- **********
                                                             3016.66667            avg
                                                                                   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
                                                                                   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
                                                                              2200 sum

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

扫码关注云+社区

领取腾讯云代金券