Oracle多行函数/组函数

SQL> --多行函数又叫组函数，作用于一组函数，返回一个数据，例如求一组数据的最大/最小值
SQL> --多行函数常用的方法有：avg(平均值)/max/min/count/sum
SQL> --求工资的总额
SQL> select sum(sal)
  2  from emp;

  SUM(SAL)                                                                      
----------                                                                      
     29025                                                                      

SQL> --求一共有多少员工
SQL> select count(*) from emp;

  COUNT(*)                                                                      
----------                                                                      
        14                                                                      

SQL> --平均工资
SQL> select sum(sal)/count(*) 一,avg(sal) 二
  2  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> --其中二三情况一样，一情况不一样。
SQL> --那是因为组函数有自动滤空的功能。
SQL> --但是情况一部是也用了组函数么？
SQL> --但是奖金的总额是滤空了，但是总人数也滤空了，但是count(*)是计算总记录条数的，不是单单计算奖金的
SQL> select count(*)，count(comm)
  2  from emp;

  COUNT(*) COUNT(COMM)                                                          
---------- -----------                                                          
        14           4                                                          

SQL> --如上可以看到奖金和所有记录的区别
SQL> --组函数都会自动滤空，但是我们也可以把自动滤空功能屏蔽掉
SQL> --只要我们在有滤空的函数上自己加上一个滤空函数
SQL> select count(*),count(nvl(comm,0)) from emp;

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

SQL> --这时候，两者的结果是一样的
SQL> --分数函数
SQL> host cls

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

    DEPTNO   AVG(SAL)                                                           
---------- ----------                                                           
        30 1566.66667                                                           
        20       2175                                                           
        10 2916.66667                                                           

SQL> --以上是求各部门的平均薪水

SQL> 注意，分组的语法要求：

SQL> /*
SQL> select a,b,c,组函数
SQL> from ***
SQL> group by a,b,c;
SQL> 如果我们查询了几个，在组函数之前的列名，都要写到group by 后面，否则报错
SQL> 但是在group by中的列名，不必都出现在select后面
SQL> */
SQL> --group by语句后面可以跟多个列
SQL> select deptno,job,sum(sal)
  2  from emp
  3  group by deptno,job
  4  order by 1;

    DEPTNO JOB         SUM(SAL)                                                 
---------- --------- ----------                                                 
        10 CLERK           1300                                                 
        10 MANAGER         2450                                                 
        10 PRESIDENT       5000                                                 
        20 ANALYST         6000                                                 
        20 CLERK           1900                                                 
        20 MANAGER         2975                                                 
        30 CLERK            950                                                 
        30 MANAGER         2850                                                 
        30 SALESMAN        5600                                                 

已选择9行。

SQL> --上面的语句作用是查询个个部门职位的总薪水
SQL> --在分组的基础之上再进行过滤，就要使用到having
SQL> --比如上面的例子中，我们得到了各个部门的平均薪水
SQL> --现在我们再求平均薪水大于2000的部门

SQL> select deptno,avg(sal)
  2  from emp
  3  group by deptno
  4  having avg(sal)>2000;

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

SQL> --那么having跟where都是进一步筛选，那么它们有什么不同呢？
SQL> --在where语句中不能使用组函数
SQL> --如果没有组函数，那么它们的作用是一样的
SQL> --查询部门号为10的平均薪水
SQL> --下面先用having
SQL> select deptno,avg(sal)
  2  from emp
  3  group by deptno
  4  having deptno=10;

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

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

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

SQL> --where语句要在group By 语句之前
SQL> --关于sql的优化问题，如果能使用where尽量时候where
SQL> --因为where在group by的前面，所以是先筛选，再分组
SQL> --但是having在group by 的后面，所以是先分组再筛选
SQL> --如果我们的数据样本很大的话，先分组效率肯定低
SQL> spool off