-----------------------------------------------分组函数
--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;
oracle 分组函数及相关测试
最新推荐文章于 2024-05-27 01:18:31 发布