第六章：oracle分组函数(多行函数)

---------------------------------------------------------
--分组函数：对多行数据进行操作
      请思考如下问题？
      查询所有员工的每个月工资总和，平均工资？
      查询工资最高和最低的工资是多少？
      查询公司的总人数？
      查询有奖金的总人数？

      五个常用的分组函数：
              min:求最小值
              max:求最大值
              sum:求和
              avg:求平均值
              count:统计
              
---------------------------------------------------------
--min和max
        注意：
              MIN和MAX可以用于任何数据类型

查询工资最高和最低的工资是多少？
select min(sal),max(sal) from emp;--数值类型
查询入职日期最早和最晚的日期
select min(hiredate),max(hiredate) from emp;-- 日期类型
select min(ename),max(ename) from emp; -- 字符类型

--SUM函数和AVG函数
        注意：
             1、SUM和AVF函数都只是能够对数值类型的列或表达式操作
             2、avg函数会忽略空值，所以一般需要排除空值的影响
             
  查询所有员工的每个月工资总和，平均工资？
  select sum(sal) from emp;--229023
  select avg(sal) from emp;--14313.9375
  
  查询所有员工的平均奖金、奖金总和
  select sum(comm) from emp;--91088
  --91088/16=5693
  select avg(comm) from emp;--18217.6  91088/5
  select avg(nul(comm,o)) from emp;--5693 avg函数会忽略控制字，所以一般需要排除空值的影响
  
  查询职位以SALES开头的所有员工平均工资、最低工资、最高工资、工资和
  select avg(nvl(sal,0)) 平均工资，min(sal) 最低工资,max(sal) 最高工资, sum(sal) 工资和
  from emp where job like 'SALES%';

  --COUNT函数 COUNT函数的主要功能是返回满足条件的每组记录条数
           注意：count(*)不会受空值的影响
                 count(列名)：会受到空值的影响：忽略null

  查询公司人数
  select * from emp;
  select count(*) from emp;--16  速度慢
  
  select empno from emp;
  select count(empno) from emp;-- 速度较快
  
  select comm from emp;
  select count(comm) from emp;--5
  
  select nul(comm,123123) from emp;--123123只是占位符
  select count(nvl(comm,123123)) from emp;--16

  select 1 from emp;--1只是占位符
  select count(1) from emp;--16 速度快
  
  查询30号部分有多少员工
  select  count(1) from emp where deptno=30;

  查询部门30有多少个员工领奖金
  select comm from emp where deptno=30;--14
  select count(comm) from emp where deptno=30;--4
  查询有员工的部门数量
  select count(deptno) from emp;--15
  select count(distinct deptno) from emp;--3 distinct:排除重复
  
  ---------------------group by子句------------------------
         注意：
             1、可以使用group by 子句先对满足where条件的数据行进行分组，再求分组后的数据
             2、其中GROUP BY 子句指定要分组的列 group by 列名 不能使用列别名
             3、在SELECT列表中除了分组函数那些项，所有列都必须包含在GROUP BY 子句中
             4.GROUP BY 所指定的并不是必须出现在SELECT 列表中
             5、按多列分组时，列是有顺序的   例如：先对部门分组再对岗位进行分组

-group by deptno ,job
--按照单列分组
  查询每个部门的平均工资?
  select avg(sal) from emp where deptno=10
  select avg(sal) ,deptno from emp group by deptno
           
  查询每个部门的编号，平均工资
  select avg(sal),deptno from emp group by deptno
  select avg(sal)  from emp group by deptno;
  
  --按多个列分组
  查询每个部门每个岗位的工资总和
  --每个部门的工资总和
  select sum(sal),deptno from emp group by deptno;
  思路：先对部门分组再对岗位进行分组
  select sum(sal),deptno,job
  from emp
  group by deptno,job
  每个部门的人数
  select count(1),deptno from emp group by deptno

–1.查询每个部门的部门编号，部门名称，部门人数，最高工资，最低工资，工资总和，平均工资。没有人数的部门也显示
select d.deptno,dname,count(1),max(sal),min(sal),sum(sal),avg(sal)
from emp e ,deptno d
where e.deptno(+)=d.deptno
group by d.deptno,dname

–2.查询每个部门，每个岗位的部门编号，部门名称，岗位名称，部门人数，最高工资，最低工资，工资总和，平均工资。
select d.deptno,dname,job,count(1),max(sal),min(sal),sum(sal),avg(sal)
from emp e,dept d
where e.deptno=d.deptno
group by d.deptno,dname,job
–3.查询每个经理所管理的人数，经理编号，经理姓名，要求包括没有下属的经理信息。
select count(1),m.empno,m.ename
from emp w,emp m
where w.mgr(+)=m.empno
group by m.empno,m.ename

select m.empno,m.ename 经理,count(w.empno)
from emp w,emp m
where w.mgr(+)=m.empno
group by m.empno,m.ename

–3.查询每个经理所管理的人数，经理编号，经理姓名，没有经理的员工也显示
select m.empno,m.ename 经理,count(w.empno)
from emp w,emp m
where w.mgr=m.empno(+)
group by m.empno,m.ename

---------------------having子句------------------------
注意：
1、对分组之后的数据进行再一次筛选
2、建议having子句要跟在group by子句之后
查询每个部门最高工资大于2900的部门编号，最高工资
select deptno,max(sal) from emp group by deptno having 2900<max(sal)
-----------------SELECT语句的执行过程(****)-------------------------------
1、通过FROM子句中找到需要查询的表；
2、通过WHERE子句进行非分组函数筛选判断；
3、通过GROUP BY子句完成分组操作；
4、通过HAVING子句完成组函数筛选判断；
5、通过SELECT句选择显示的列或表达式及函数；
6.通过ORDER BY子句进行排序操作。

查询岗位是’SALESMAN’,‘MANAGER’,'CLERK’的部门编号，每个部门的最高工资并且最高工资大于2900
select deptno, max(sal) --5
from emp --1
where job in (‘SALESMAN’, ‘MANAGER’, ‘CLERK’) --2
group by deptno --3
having 2900 < max(sal); --4

查询每个部门最高工资大于2900的部门编号，最高工资 ,部门名称,并按照最高工资进行降序排序
select d.deptno,max(sal),dname
from emp e,dept d
where e.deptno=d.deptno
group by d.deptno,dname
having 2900<max(sal)
order by max(sal) desc

select d.deptno,max(sal) maxsal,dname --5
from emp e,dept d --1
where e.deptno=d.deptno --2
group by d.deptno,dname --3
having 2900<max(sal) --4
order by maxsal desc --6

查询每个部门的部门编号、部门名称、部门人数
select d.deptno,dname,count(1)
from emp e,dept d
where e.deptno=d.deptno
group by d.deptno,dname,

–组函数的嵌套最多能嵌套两个函数但是单行行数可以随意嵌套
–显示部门的平均薪水的最大值
select max(avg(sal)) from emp group by deptno;
select round(round(round(1234.56))) from dual;–1235

------------------------------巩固练习---------------------------------------
–1、查询每个部门的平均工资及其部门名称、部门编号
select d.deptno,avg(nvl(sal,0)),dname
from emp e,dept d
where e.deptno,d.deptno
group by d.deptno,dname
–2、查询每个部门的最高工资及其部门名称、部门编号
select d.deptno,max(sal),dname
from emp e,dept d
where e.deptno,d.deptno
group by d.deptno,dname
–3.查询每个经理所管理的人数，经理编号，经理姓名，要求包括没有下属的经理信息。
select count(w.empno),m.empno,m.ename
from emp w,emp m
where w.mgr(+)=m.empno
group by m.empno,m.ename
----------------------------------课堂练习-----------------------------------
–1.查询部门人数大于2的部门编号，部门名称，部门人数。
select d.deptno,dname,count(1)
from emp e,dept d
where e.deptno=d.deptno
group by d.deptno,dname
having count(1)>2

select d.deptno,dname,count(e.empno) empnum
from dept d,emp e
where d.deptno=e.deptno
group by d.deptno,dname
having 2<count(e.empno)

–2.查询部门平均工资大于2000，且人数大于2的部门编号，部门名称，部门人数，部门平均工资，并按照部门人数升序排序。
select d.deptno,dname,count(1),avg(sal)
from emp e,dept d
where e.deptno=d.deptno
group by d.deptno,dname
having avg(sal)>2000 and count(1)>2
order by count(1)

----------------------------------课后练习-----------------------------------------
1.查询部门平均工资在2500元以上的部门名称及平均工资。
select dname,avg(nvl(sal,0))
from emp e,dept d
where e.deptno=d.deptno
group by dname
having 2500<avg(nvl(sal,0))
2.查询员工岗位中不是以“SA”开头并且平均工资在2500元以上的岗位及平均工资，并按平均工资降序排序。
select job,avg(nvl(sal,0)) avgsal
from emp
group by job
having job not like ‘SA%’ and 2500<avg(nvl(sal,0))
order by avgsal desc

select job,avg(nvl(sal,0)) avgsal
from emp
where job not like ‘SA%’
group by job
having 2500<avg(nvl(sal,0))
order by avgsal desc

3.查询部门人数在2人以上的部门名称、最低工资、最高工资,并对求得的工资进行四舍五入到整数位。
select dname,round(min(sal)),round(max(sal))
from emp e,dept d
where e.deptno=d.deptno
group by dname
having 2<count(e.empno)

4.查询岗位不为SALESMAN，工资和大于等于2500的岗位及每种岗位的工资和。
select job,sum(sal)
from emp
where job <>‘SALESMAN’
group by job
having sum(sal)>2500

5.显示经理号码和经理姓名，这个经理所管理员工的最低工资，没有经理的KING也要显示，不包括最低工资小于3000的，按最低工资由高到低排序。
select w.mgr,m.ename,min(w.sal) minsal
from emp w,emp m
where w.mgr=m.empno(+)
group by w.mgr,m.ename
having min(w.sal)>=3000
order by minsal

6.写一个查询，显示每个部门最高工资和最低工资的差额
select max(nvl(sal,0))-min(nvl(sal,0))
from emp
group by deptno

select * from emp;
select * from emp;