一个菜鸟的oracle之路--------二(续)(二)
分组查询 group by **
案例31 按部门计算每个部门的最高和最低薪水是多少
[sql]
select deptno,max(salary),min(salary) from emp_xxx group by deptno;
案例32 计算每个部门的薪水总和和平均薪水
[sql]
select deptno,sum(salary),avg(salary)from emp_xxx group by deptno;
案例33 每个部门的统计信息
[sql]
select deptno,max(salary) max_s,min(salary) min_s,sum(salary) sum_s
from emp_xxx group by deptno;
案例34 按职位分组，每个职位的最高最低薪水和人数
[sql]
select job,max(salary) max_s,min(salary) min_s,count(*) emp_num
from emp_xxx group by job order by emp_num;
案例35 如果select后没有被组函数的列，没有出现在group by 短语中，会出错
[sql]
select deptno,max(salary) max_s,
min(salary) min_s,
sum(salary) sum_s,
avg(nvl(salary,0)) avg_s,
count(*) emp_num
from emp_xxx;
//此查询语句是错误的，原因deptno不是单组分组函数
案例36 如果group by 短语中的列，没有出现在select短语中，不会出错，信息不够全
[sql]
select max(salary) max_s,
min(salary) min_s,
sum(salary) sum_s,
avg(nvl(salary,0)) avg_s,
count(*) emp_num
from emp_xxx
group by deptno;
select deptno,max(salary) max_s,
min(salary) min_s,
sum(salary) sum_s,
avg(nvl(salary,0)) avg_s,
count(*) emp_num
from emp_xxx
group by deptno;
//如果group by 短语中的列，没有出现在select短语中，不会出错，信息不够全
having 子句
having 自己用于对分组后的数据进行过滤。
注意区别where是对表中数据的过滤，having是堆分组得到的结果数据进一步过滤。
案例37 平均薪水大于5000元的部门数据，没有部门的不算在内？
[sql]
select deptno ,avg(nvl(salary,0)) avg_s
from emp_xxx
where deptno is not null
group by deptno having
avg(nvl(salary,0))>5000 ;
案例38 考察的还是having 对分组得到的结果进一步过滤功能
薪水总和大于20000元的部门数据
[sql]
select deptno,avg(nvl(salary,0)) avg_s,sum(salary)
from emp_xxx
where deptno is not null
group by deptno
having sum(salary)>20000;
案例39 哪些职位的人数超过2名
[sql]
select job ,count(*) emp_num
from emp_xxx
where job is not null
group by job
having count(*)>2
order by emp_num;
//这一个案例很经典，我感觉还没有完全掌握
//order by 一定要放在最后
子查询
案例40 查询最高薪水是谁
[sql]
select max(salary) max_s from emp_xxx;
查询每个部门的最高薪水是
[sql]
select deptno,max(salary) max_s from emp_xxx group by deptno;
//查询到该表中最高的薪水是
//利用到子查询的知识
[sql]
select ename from emp_xxx
where salary=(select max(salary) max_s from emp_xxx);
案例41 查询最低薪水的人是谁？
//首先更新数据，方法如下
[sql]
update emp_xxx
set salary=100
where ename='郭靖';
//查询最低薪水的人是
[sql]
select ename ename_min,deptno deptno_min
from emp_xxx
where salary=(select min(salary) from emp_xxx);