oracle 分组函数及相关测试

diaa96n5ut

　　-----------------------------------------------分组函数
--1,如果数据库没有的数据，count(*)返回的不是null，而是0

--2,Avg Max Min Sum

--3,分组函数与空值
  --分组函数省略列中的空值
  select avg(comm) from emp;
  select sum(comm) from emp;
  --可以使用nvl()函数强制分组函数处理空值
  select avg(nvl(comm,0)) from emp;
--4,group by 子句
  --出现在select列表的字段或者出项在order by后面的字段,
  --如果不是包含在分组函数中,那么该字段必须同时在group by
  --子句中出现,包含在group by子句中的字段则不必须出现在select
  --列表中。可以使用where子句限定查询条件，可以使用order by指定排序方式
 
  --如果没有group by子句，select列表中不允许出现字段（单行函数）与
  --分组函数混用的情况
  select empno,sal from emp;
  select avg(sal) from emp;
  select empno,initcap(ename),avg(sal),from emp;--(非法)
 
  --不允许在where子句中使用分组函数
  select deptno,avg(sal)as a
  from emp
  --where avg(sal)>2000;
  group by deptno;
 
  --那么怎么得到部门平均工资>2000的部门列表呢？
  select dept.deptno 部门编号,dept.dname 部门,a.asal 部门平均工资
  from dept
  join (select deptno,avg(sal) asal from emp
                 group by deptno)a
  on dept.deptno=a.deptno
  where a.asal>1000
 
--5，having子句
  select deptno,job,avg(sal)
  from emp
  where hiredate>=to_date('1981-05-01','yyyy-mm-dd')
  group by emp.deptno,job
  having avg(sal)>1200
  order by deptno,job
  --要是要求在上调查询中加入部门名称呢？
  select deptno,dname,job,
--6,分组函数的嵌套
  select max(avg(sal)) from emp group by deptno;
--7,分组统计各部门下工资>500的员工的平均工资
select deptno,nvl(avg(sal),0) from emp where sal>2500 group by deptno;
--8,统计部门下平均工资大于500的部门
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;
--9,算出部门30中得到最多奖金的员工的奖金
select max(comm) from emp where deptno=30;
  --如果要显示他是谁呢？
  select * from emp a where a.comm = (select max(b.comm) from emp b where b.deptno=30);
--10,算出每个职位的员工数和最低工资
select job 职位,count(*) 在职人数,min(sal) 最低工资 from emp group by job;
--11,算出每个部门每个职位的平均工资和平均奖金（平均值包括没有奖金），如果平均奖大于300
--显示'奖金不错',如果平均奖100-300显示'奖金一般'，如果奖金小于100,显示'基本没有奖金'
--按部门编号降序,平均工资降序
select deptno 部门,job 职位,avg(sal) 同职位平均工资,avg(nvl(comm,0)) 同职位平均奖金,case
  when avg(nvl(comm,0))>300 then '奖金不错'
  when avg(nvl(comm,0))<300 and avg(nvl(comm,0))>100 then '奖金一般'
  when avg(nvl(comm,0))<100 then '奖金太糙'
  end 奖金
from emp group by job,deptno order by deptno desc,job desc,avg(sal) desc;
 
  --如果要显示部门后面对应的职位的对应情况呢？(在这里发现表不合理，同一部门同意职位的奖金也是不相同的2)
  select deptno,job,comm from emp group by empno, deptno,job,comm order by deptno;
--12,列出员工表中每个部门的员工数和部门编号
select deptno 部门编号,count(deptno) 部门人数 from emp group by deptno
--13,得到工资大于自己部门平均工资的员工信息
create or replace view avg_sal_deptno as select deptno deptno,avg(sal) avg_sal from emp group by deptno;
select * from avg_sal_deptno;
select * from emp join avg_sal_deptno on emp.sal>avg_sal_deptno.avg_sal and emp.deptno=avg_sal_deptno.deptno;

select * from emp a join
(select deptno deptno,avg(sal) avg_sal from emp group by deptno) b
on a.deptno=b.deptno and a.sal>b.avg_sal;
--14,分组统计每个部门下,每种职位的平均奖金（也要算没有奖金的人）和总工资（包括奖金）
--注明：此题有问题：每个部门每个人的总工资是不一样的，所以只能按不同的人来求
   --同部门同职位的平均工资
   create or replace view v_avg_sal_dept_job as select deptno,job,avg(sal) avg_sal from emp group by deptno,job order by deptno
   select * from v_avg_sal_dept_job

select a.empno 员工编号,a.deptno 所在部门编号,v_avg_sal_dept_job.avg_sal 所在部门同工作平均工资,a.sal+nvl(a.comm,0) 总工资 from emp a join v_avg_sal_dept_job
on a.deptno = v_avg_sal_dept_job.deptno
and a.job=v_avg_sal_dept_job.job;