oracle中与分组函数有关的多表查询的经典例子

假设有如下三张表

1.部门表

2.员工表

3.职位表

结合以上三张表，可有如下查询

--分组统计各部门下平均工资大于3000的员工的平均工资
select deptId,avg(salary) 平均工资 
from employee 
group by deptid 
having avg(salary)>3000;
---统计各部门下平均工资大于5000的部门


select t.avgsal,t.did,d.deptname from 
  (select avg(salary) avgsal,deptid did
  from employee 
  group by deptid
  having avg(salary)>4000) t,dept d
  
where t.did=d.deptid;


--算出每个职位的员工数量和最低工资
select j.jobname,t.c,t.m  from
  (select jobid,count(empid) c,min(salary) m
  from employee 
  group by jobid) t,jobs j
 where t.jobid=j.jobid;
 
 select * from jobs t;
 --列出员工表中每个部门的员工数和部门编号
 
 select count(*),deptid 
 from employee 
 group by deptid;
 
 --得到工资大于自己部门平均工资的员工信息
 select t.*,e.empname,e.salary 
 from  
    ( select deptid,avg(salary) avgsal
     from employee 
     group by deptId ) t,employee e
 where e.salary>t.avgsal 
 and e.deptid=t.deptid;
 
 insert into employee (hiredate) values(to_date('2014-02-14','yyyy-mm-dd')) where empid=1;


 --查询入职时间比自己部门领导入职时间早的员工信息
 
 select e.* 
 from  
 (select empname,manager,hiredate,empid 
 from employee 
 where manager=empname) t,employee e
 where t.manager=e.manager and e.hiredate<t.hiredate
 --列出员工表中每个部门的员工数（员工数必须大于3）和部门名称
 select deptname,t.* from
 (select deptid,count(*) 
 from employee 
 group by deptid 
 having count(*)>2) t,dept d
 where t.deptid=d.deptid;
 
 --以职位分组，找出平均工资最高的两位职位
 select p.* from
   (select t.jobid td,t.av ta,j.jobname jj from 
       (select jobid,avg(salary) av
       from employee 
       group by jobid ) t,jobs j
    where t.jobid=j.jobid 
    order by ta desc) p
 where rownum<=2 order by p.ta desc;
 
 --找出不在部门20，且比部门20中任何一个人工资都高的员工姓名，部门名称
 select t.*,deptname from
 (select salary,deptid,empname 
 from employee 
 where deptid not in 5 and salary>
       (select max(salary) from employee where deptid=5)) t,dept d
 where d.deptid=t.deptid;
 
--得到平均工资大于 2000 的工作职种
   select t.*,j.jobname from 
  (select avg(salary) avgsal,jobid 
  from employee 
  group by jobid) t,jobs j 
  where j.jobid=t.jobid and t.avgsal>4000 order by t.avgsal desc;


--分部门得到工资大于5000的所有员工的平均工资，并且平均工资还要大于5000
   
  select avg(salary),deptid from
  (select salary ,deptid from employee where salary>5000) 
  group by deptId 
  having avg(salary)>5000
   
--得到每个月工资总数最少的那个部门的部门编号，部门名称，部门位置
   select t.did,d.deptname,d.deptloc from
     (select sum(salary) susal,deptid did
      from employee 
      group by deptid 
      order by susal) t,dept d
    where rownum=1 and t.did=d.deptid;


--查找出部门 1 和部门 5 中，工资最高第 2名到工资第 4 名的员工的员工名字，部门名字，部门位置
select p.*,d.deptname,d.deptloc from
  (select t.*,t.deptid tid from
      (select deptid,empname,salary
      from employee 
      where deptid in(1,5) 
      order by salary desc) t
  where rownum<=4) p,dept d
where p.tid=d.deptid and rownum<=2;


--查找出收入,下级比自己上级还高的员工编号，员工名字，员工收入
  
   select t.salary 上司工资,e.empname,e.salary from
      (select empid,empname,salary 
       from employee
       where empname=manager) t,employee e
   where e.salary>t.salary 
   and e.manager=t.empname;


--查找出工资不为6000的员工的员工名字，部门名字，部门位置
select employee.*,dept.deptname,dept.deptloc 
from employee,dept 
where  salary not in
       (select salary from employee where salary=6000)
and employee.deptid=dept.deptid;


--查找出不属于任何部门的员工
select * from employee where deptid is null;