Oracle多行函数（组函数）

作用于多条数据 返回一条数据

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> select sum(comm)/count(*) 一, sum(comm)/count(comm) 二, avg(comm) 三 from emp;

        一         二         三                                                
---------- ---------- ----------                                                
157.142857        550        550                                                

SQL> --空值 4. 组函数会自动滤空
SQL> select count(*),count(comm) from emp;

  COUNT(*) COUNT(COMM)                                                          
---------- -----------                                                          
        14           4      
SQL> select count(*),count(nvl(comm,0)) from emp;

  COUNT(*) COUNT(NVL(COMM,0))                                                   
---------- ------------------                                                   
        14                 14    
SQL> --可以在组函数中嵌套滤空函数，来屏蔽组函数的滤空功能



SQL> --求各个部门的平均工资
SQL> select deptno,avg(sal)
  2  from emp
  3  group by deptno;
group by depnto
SQL> --语法
SQL> select deptno,job,avg(sal)
  2  from emp
  3  group by depnto;
group by depnto
         *
第 3 行出现错误: 
ORA-00904: "DEPNTO": 标识符无效 
SQL>--在oracle中所有没有被组函数包括的列 必须出现在group by的后面  
SQL> --group by多列：先按照第一列分；如果第一列相同，再按照第二列分
SQL> ed
已写入 file afiedt.buf

  1  select deptno,job,avg(sal)
  2  from emp
  3  group by deptno,job
  4* order by 1
SQL> /

    DEPTNO JOB         AVG(SAL)                                                                                                                       
---------- --------- ----------                                                                                                                       
        10 CLERK           1300                                                                                                                       
        10 MANAGER         2450                                                                                                                       
        10 PRESIDENT       5000                                                                                                                       
        20 ANALYST         3000                                                                                                                       
        20 CLERK            950                                                                                                                       
        20 MANAGER         2975                                                                                                                       
        30 CLERK            950                                                                                                                       
        30 MANAGER         2850                                                                                                                       
        30 SALESMAN        1400                                                                                                                       

已选择9行。


SQL> --按部门，不同的职位统计平均工资
SQL> --having
SQL> --查询平均工资大于2000的部门
SQL> ed
已写入 file afiedt.buf

  1  select deptno,avg(sal)
  2  from emp
  3  group by deptno
  4* having avg(sal)>2000
SQL> /
SQL> --having 过滤分组
SQL> --having和where的区别
SQL> --求10号部门的平均工资
SQL> select deptno,avg(sal)
  2  from emp
  3  group by deptno
  4  having deptno=10;

    DEPTNO   AVG(SAL)                                                                                                                                 
---------- ----------                                                                                                                                 
        10 2916.66667                                                                                                                                 

SQL> select deptno,avg(sal)
  2  from emp
  3  where deptno=10
  4  group by deptno;

    DEPTNO   AVG(SAL)                                                                                                                                 
---------- ----------                                                                                                                                 
        10 2916.66667  	
SQL> --SQL优化  3. 尽量使用where
SQL> --             例外：如果条件中含义组函数，只能使用having

制作报表是的group by 语句增强

SQL> --group by的增强
SQL> /*
SQL> group by deptno,job
SQL> +
SQL> group by deptno
SQL> +
SQL> group by null
SQL> 
SQL> =
SQL> 
SQL> group by rollup(deptno,job)
SQL> group by rollup(a,b)
SQL> =
SQL> group by a,b
SQL> +
SQL> group by a
SQL> +
SQL> group by null
SQL> 
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                                                                                                                       

    DEPTNO JOB         SUM(SAL)                                                                                                                       
---------- --------- ----------                                                                                                                       
        30                 9400                                                                                                                       
                          29025                                                                                                                       

已选择13行。
SQL> --SQLPLUS支持报表功能
SQL> break on deptno skip 2
SQL> select deptno,job,sum(sal)
  2  from emp
  3  group by rollup(deptno,job);

    DEPTNO JOB         SUM(SAL)                                                                                                                       
---------- --------- ----------                                                                                                                       
        10 CLERK           1300                                                                                                                       
           MANAGER         2450                                                                                                                       
           PRESIDENT       5000                                                                                                                       
                           8750                                                                                                                       
                                                                                                                                                      
                                                                                                                                                      
        20 CLERK           1900                                                                                                                       
           ANALYST         6000                                                                                                                       
           MANAGER         2975                                                                                                                       
                          10875                                                                                                                       
                                                                                                                                                      

    DEPTNO JOB         SUM(SAL)                                                                                                                       
---------- --------- ----------                                                                                                                       
                                                                                                                                                      
        30 CLERK            950                                                                                                                       
           MANAGER         2850                                                                                                                       
           SALESMAN        5600                                                                                                                       
                           9400                                                                                                                       
                                                                                                                                                      
                                                                                                                                                      
                          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                                                                                                                       

    DEPTNO JOB         SUM(SAL)                                                                                                                       
---------- --------- ----------                                                                                                                       
        30                 9400                                                                                                                       
                          29025                                                                                                                       

已选择13行。