Oracle入门基础（四）一一多行函数

SQL> --工资总额
SQL> select sum(sal) from emp;

  SUM(SAL)                                                                                                                                                                                                                                                                                                                                                                                            
     29025                                                                                                                                                                                              

SQL> --人数
SQL> select count(*) from emp;

  COUNT(*)                                                                                                                                                                                                                                                                                                                                                                                         
        14                                                                                                                                                                                              

SQL> --平均工资
SQL> select sum(sal)/count(*) 一,avg(sal) 二 from emp;
        一         二                                                                                                                                                                                                                                                                                                                                                                  
2073.21429 2073.21429                                                                                                                                                                                   

SQL> --平均奖金
SQL> select sum(comm)/count(*) 一,sum(comm)/count(comm) 二,avg(comm) 三
  2  from emp;
        一         二         三                                                                                                                                                                                                                                                                                                                                            
157.142857        550        550                                                                                                                                                                        

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

SQL> select * from emp;
 EMPNO ENAME      JOB    MGR HIREDATE      SAL       COMM  DEPTNO                                                                                                                                                                                                                       
      7369 SMITH      CLERK           7902 17-12月-80            800                    20                                                                                                              
      7499 ALLEN      SALESMAN        7698 20-2月 -81         1600        300       30                                                                                                              


SQL> --null值 5. 组函数会自动滤空；
SQL> select count(*), count(nvl(comm,0)) from emp;

  COUNT(*) COUNT(NVL(COMM,0))                                                                                                                                                                                                                                                                                                                                                
        14                 14                                                                                                                                                                           

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

SQL> --每个部门的平均工资
SQL> select deptno,avg(sal)  from emp group by deptno;
    DEPTNO   AVG(SAL)                                                                                                                                                                                                                                                                                                                                                                  
        30 1566.66667                                                                                                                                                                                   
        20       2175                                                                                                                                                                                   
        10 2916.66667                                                                                                                                                                                   

SQL> --多个列的分组
SQL> select deptno,job,sum(sal) from emp group by deptno,job order by 1;
    DEPTNO JOB         SUM(SAL)                                                                                                                                                                                                                                                                                                                                                
        10 CLERK           1300                                                                                                                                                                         
        10 MANAGER         2450                                                                                                                                                                                                                                                                                                                                  
        20 ANALYST         6000                                                                                                                                                                         
        20 CLERK           1900                                                                                                                                                                         
      
SQL> --多个列的分组: 先按照第一个列分组，如果相同，再第二个列分组，以此类推
SQL> --查询平均工资大于2000的部门
SQL> select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;
    DEPTNO   AVG(SAL)                                                                                                                                                                                                                                                                                                                                                              
        20       2175                                                                                                                                                                                   
        10 2916.66667                                                                                                                                                                                   

SQL> --where和having的区别：where不能使用多行函数
SQL> --查询10号部门的平均工资
SQL> select deptno,avg(sal) from emp group by deptno having deptno=10;
    DEPTNO   AVG(SAL)                                                                                                                                                                                                                                                                                                                                                                 
        10 2916.66667                                                                                                                                                                                   

SQL> ed
已写入 file afiedt.buf

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

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

SQL> --SQL优化 3. 尽量使用where

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> select deptno,job,sum(sal) from emp group by rollup(deptno,job)
    DEPTNO JOB         SUM(SAL)                                                                                                                                                                                                                                                                                                                                        
        10 CLERK           1300                                                                                                                                                                         
        10 MANAGER         2450                                                                                                                                                                                                                                                                                                                          
        10                 8750                                                                                                                                                                         
        20 CLERK           1900                                                                                                                                                                         
        20 ANALYST         6000                                                                                                                                                                                                                                                                                                                             
        20                10875                                                                                                                                                                         
        30 CLERK            950                                                                                                                                                                         
        30 MANAGER         2850                                                                                                                                                                         
        30 SALESMAN        5600                                                                                                                                                                         

SQL> break on deptno skip 2
SQL> select deptno,job,sum(sal) from emp 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                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
        30 CLERK            950                                                                                                                                                                         
           MANAGER         2850                                                                                                                                                                         
           SALESMAN        5600                                                                                                                                                                         
                           9400                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
                          29025                                                                                                                                                                         
                                                                                                                                                                                                        
                                                                                                                                                                                                      

SQL> break on null