oracle 子查询 经典总结

-----------------------------------------------子查询
--1,单行子查询
--查看工资高于工号为7566员工的所有员工信息

select * from emp
where sal>(select sal from emp where empno=7566);
--2,子查询空值、多值问题
--如果子查询未返回任何行，则主查询也不会返回任何结果
select * from emp where sal>nvl((select sal from emp where empno =8888),0);
select nvl((select sal from emp where empno =8888),0) from dual;
--3,如果子查询返回单行结果，则为单行子查询，可以在主查询中对其使用相应的单行记录比较运算符
select * from emp where sal>(select sal from emp where empno=7566);
--4,如果子查询返回赴欧行结果，则为多行子查询，此时不允许对其使用单行记录比较运算符


--5，多行子查询
select * from emp where sal > any(select avg(sal) from emp group by deptno);
select * from emp where sal > all(select avg(sal) from emp group by deptno);
select * from emp where job in(select job from emp where ename = 'MARTIN' or ename = 'SMITH');
--6,TopN查询
select * from emp where rownum = 1 or rownum = 2;
select * from (select * from emp order by sal desc) where rownum <=5;

--7,分页查询
select * from (select rownum no,e.* from
(select * from emp order by sal desc )e
where rownum<=5) where no>3;

select * from (select rownum no,e.* from
(select * from emp order by sal desc) e )
where  no>3 and no<=5;

--8,exists的执行流程
select * from t1 where t1 where exists (select null from t2 where y=x)
--可以理解成
 for  x in(select * from t1)
 loop
  if(exists(select null from t2 where y=x))
   then
    output the record
   end if
  end loop
--对于in和exists的性能区别：
 --如果子查询独处的结果集记录较少，主要查询中格的表较大且又有索引时应该用in，
 --反之若果外层的猪查询记录较少，子查询种的表大，又有索引时使用exists
 --区分in和exists 主要是造成了驱动顺序的改变（这是性能变化的关键），如果是
 --exists,那么以外层表为驱动表，先被访问，如果是in那么先执行子查询，所以我们会以
 --驱动表的快速返回为目标，那么就会考虑到索引及结果集的关系了另外in是不对null进行处理
 
 --如 select 1 from dual where null in (0,1,1,null);
 
 --练习
 --1,列出员工表中每个部门的员工数，和部门no
 select deptno,count(*) from emp group by deptno;
 --2,列出员工表中每个部门的员工数（员工数必须大于3），和部门名称
 select d.*,ed.cou from dept d,
 (select deptno,count(*) cou from emp group by deptno having count(*)>3) ed where d.deptno=ed.deptno;
 --3,找出工资比jones高的员工
 select * from emp where sal>=(select sal from emp where lower(ename)='jones')
 --4,列出所有员工的姓名和其上级的姓名
 select e1.ename as lower,e2.ename as upper from emp e1,emp e2 where e1.mgr=e2.empno;
 select e1.ename,e1.job,e2.ename,e2.job from emp e1,emp e2 where e1.mgr=e2.empno;
 --5,以职位分组,找出平均工资最高的两种职位
select * from (select job,avg(sal) a from emp group by job order by a desc) where rownum <3;
 --6,查找不在部门20,且比部门20中任何一个人工资都高的员工姓名
select ename,sal from emp join (select min(sal) m from emp where deptno = 20) a on emp.deptno <> 20 and a.m<emp.sal
 --7,得到平均工资大于2000的工作职种
select * from (select job,avg(sal) a from emp group by job) b where b.a>2000 order by b.a desc
 --8,分部门得到工资大于2000的所有员工的平均工资,并且平均工资还要大于2500
 select deptno,avg(sal),count(*) 部门工资大于2000的人数 from emp where sal >2000 group by deptno
 select deptno,avg(sal) from emp group by deptno
 select sal from emp where deptno = 30;
 --9,得到每个月工资总数最多的那个部门的部门编号,名称,部门所在地,工资总数,部门人数
 select a.d from (select deptno d,sum(sal) s,count(*) from emp group by deptno order by s desc)a where rownum<2
 select * from DEPT join(select * from (select deptno d,sum(sal) s,count(8) from emp group by deptno order by s desc)a where rownum<2)b on dept.deptno = b.d;
 select max(sum(sal)) from emp group by deptno
 --10,分部门得到平均工资等级为2级（等级表）的部门编号
 select b.dno from salgrade sa,(select deptno as dno,avg(sal) as avgsal from emp
 group by deptno) b where sa.grade=4 and b.avgsal between sa.losal and sa.hisal;
 --11,查找出部门10和部门20中，工资最高的第3名到工资第5名的员工的员工名字，部门名字，部门所在
 select * from (select  rownum no,e.* from
 (select empno,sal,emp.deptno,dname,loc from emp,dept where emp.deptno=dept.deptno and emp.deptno between 10 and 20 order by emp.sal desc) e)
 where no between 3 and 5;
 --12,查找出收入（工资+奖金），下级比自己上级还高的员工编号，姓名，收入
 select empno 工资高于上级的员工的编号,ename 工资高于上级的员工的姓名,sal+nvl(emp.comm,0) 工资高于上级的员工的总收入 from emp join (select empno eno,sal+nvl(emp.comm,0) c from emp) a
 on emp.mgr=a.eno and emp.sal+nvl(emp.comm,0)>a.c
 --13,查出工资等级不为4级的员工的员工名字，部门名字，部门位置
 select emp.deptno,emp.ename,emp.sal from emp,salgrade where sal between losal and hisal and grade<>4
 select a.en,dept.dname,dept.loc from dept
 join(select emp.deptno d,emp.ename en,emp.sal es from emp,salgrade where sal between losal and hisal and grade<>4
 ) a on dept.deptno = a.d
 --14,查找出职位和MARTIN或者SMITH一样的平均员工的平均工资
 select avg(a.sal) from
 (select * from emp where job =
 (select job from emp where ename='MARTIN') or job = (select job from emp where ename='SMITH'))a;
 
 select avg(sal) from emp where job in(select job from emp where ename = 'MARTIN' or ename = 'SMITH');
 
 select avg(sal) from emp where job in(select job from emp where ename in('MARTIN','SMITH'));

 
 --15,查找不属于任何部门的员工
 select * from emp where deptno is null or deptno not in(select deptno from dept);
 
 --16,按部门统计员工数,查出员工数最多的部门中工资第二名到第五名的所有员工信息
 select * from(select rownum N,e.* from (select emp.* from emp where deptno =
 (select deptno from (select deptno,count(*) c from emp group by deptno order by c desc) where rownum =1)
 order by sal desc)e) where N between 2 and 5;
 
 --17,查出king所在部门的部门号\部门名称\部门人数
 select a.deptno,dname,a.c from dept join
 (select deptno,count(*) c from emp where emp.deptno=(select deptno from emp where ename = 'KING')group by deptno) a on
 dept.deptno = a.deptno
 
 --18,查出king所在部门工作年限最大的员工名字
select * from emp where hiredate = (select min(hiredate) from emp where emp.deptno = (select deptno from emp where ename='KING'));
 --19,查出工资成本最高的部门的部门编号和部门名称
 select emp.deptno,dept.dname from emp,dept
 where nvl(emp.mgr,0)+sal = (select max(nvl(emp.mgr,0)+sal) from emp)
 and emp.deptno = dept.deptno;