笔记系列----sql分组函数

set define on 使用&来区分变量

set define off 关闭&的使用

&&用于引用&的变量

分组函数

1.重要的分组函数

sum(),avg(),count(),max(),min()

select max(sal) from emp group by deptno;//deptno是分组特性，max（）是分组函数

select empno,sum(sal) from emp group by deptno;//empno 14 个数  deptno 5个数 会不一致

若是sal中有空值，则count(sal)  count(*)值一定不一样

max min count可用于日期和字符，但是sum avg不可

select count(*),count(comm) from dep;

select deptno ,sum(comm) from emp group by deptno;

select deptno ,sum(nvl(comm,0)) from emp group by deptno;

2.在函数中使用nvl函数

3.group by创建组

若使用group by   则select 后面只有2种可能：组函数，分组特性

对分组结果过滤：

having的限定选项：

聚合函数   &   分组特性

having和where区别

having 分组完后再筛选

where  对基表筛选

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

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

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

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

对比having与where

SQL> select empno,sal from emp where sal>2000;

     EMPNO        SAL
---------- ----------
      7566       2975
      7698       2850
      7782       2450
      7788       3000
      7839       5000
      7902       3000

6 rows selected.

SQL> select empno,sal,deptno from emp where sal>2000;

     EMPNO        SAL     DEPTNO
---------- ---------- ----------
      7566       2975         20
      7698       2850         30
      7782       2450         10
      7788       3000         20
      7839       5000         10
      7902       3000         20

6 rows selected.

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

    DEPTNO   SUM(SAL)
---------- ----------
        30       2850
        20       8975
        10       7450

SQL> select deptno,sum(sal) from emp where sal>2000 group by deptno having sum(sal)>8000;

    DEPTNO   SUM(SAL)
---------- ----------
        20       8975

再添加order by语句

SQL> select deptno,sum(sal) from emp where sal>2000 group by deptno having sum(sal)>7000;

    DEPTNO   SUM(SAL)
---------- ----------
        20       8975
        10       7450

SQL> select deptno,sum(sal) from emp where sal>2000 group by deptno having sum(sal)>7000 order by 2;

    DEPTNO   SUM(SAL)
---------- ----------
        10       7450
        20       8975

执行顺序

     

select	from	where	group by	having	order by
5	1	2	3	4	6

而关于select和order by的先后执行顺序有争议

私以为应该是先select再order by，因为order by 2模式下是依据select的选择内容，来排序的

4.分组函数的嵌套

最多可嵌套2层

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

  SUM(SAL)
----------
      9400
     10875
      8750

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

AVG(SUM(SAL))
-------------
	 9675

SQL> select count(avg(sum(sal))) from emp group by deptno;
select count(avg(sum(sal))) from emp group by deptno
                 *
ERROR at line 1:
ORA-00935: group function is nested too deeply