SQL和PLSQL--多行函数

SQL> --工资总额

SQL> select sum(sal) from emp;

SQL> --人数

SQL> select count(*) from emp;

SQL> --平均工资

SQL> select sum(sal)/count(*) 一,avg(sal) 二 from emp;

SQL> --平均奖金

SQL> --null 5.

SQL> select sum(comm)/count(*) 一,sum(comm)/count(comm) 二, avg(comm) 三

  2  from emp;

SQL> select count(*),count(comm) from emp;

SQL> select * from emp;

SQL> --null 5. 组函数自动滤空

SQL> select count(*),count(nvl(comm,0)) from emp;

SQL> --null 5. 组函数自动滤空; 可以嵌套滤空函数来屏蔽他的滤空功能

SQL> host cls

SQL> --分组数据

SQL> --求每个部门的平均工资

SQL> select deptno,avg(sal)

  2  from emp

  3  group by deptno;

SQL> --多个列的分组

SQL> select deptno,job,sum(sal) from emp group by deptno,job order by 1;

SQL> --求平均工资大于2000的部门

SQL> select deptno,avg(sal)

  2  from emp

  3  group by deptno

  4  having avg(sal) > 2000;

SQL> --where和having最大的区别：where后面不能使用组函数

SQL> --求10号部门的平均工资

SQL> select deptno,avg(sal)

  2  from emp

  3  group by deptno

  4  having deptno=10;

SQL> ed

已写入 file afiedt.buf

  1  select deptno,avg(sal)

  2  from emp

  3  where deptno=10

  4* group by deptno

SQL> /

SQL> --SQL原则 3： 尽量使用where

SQL> host cls

SQL> /*

SQL> group by的增强

SQL> select deptno,job,sum(sal) from emp group by deptno,job

SQL> +

SQL> select deptno,sum(sal) from emp group by deptno

SQL> +

SQL> select sum(sal) from emp

SQL> 

SQL> ====

SQL> 

SQL> select deptno,job,sum(sal) from emp group by rollup(deptno,job);

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) from emp group by rollup(deptno,job);

SQL> break on deptno skip 2

SQL> select deptno,job,sum(sal) from emp group by rollup(deptno,job);

SQL> break on null

SQL> /

SQL> spool off