oracle学习总结-----子查询

子连接：(子查询一般不超过四层)


--需求：查询在NEW YORK工作的员工信息(需要用多值连接符in)
select * from emp where deptno in (select deptno from dept where loc='NEW YORK');




多列子查询：
 ---需求：如何查询与smith的部门与岗位相同的所有雇员。
   select * from emp where (deptno,job) = (select from deptno,job from emp where ename = 'SMITH');




from字句中使用子查询
  ---需求：如何显示高于自己部门平均工资的员工信息
      思路：将子查询的结果当成临时表
   select ename,sal,t2.avgsal,emp.deptno from emp,
    (select avg(sal) avgsal ,deptno from emp group by deptno) t2
    where emp.deptno = t2.deptno and emp.sal >t2.avgsal;
  
 ---需求：查询本部门最高工资的人员详细信息
     select emp.* ,t2.maxsal from emp,
      (select max(sal) maxsal,deptno from emp group by deptno) t2
       where emp.deptno = t2.deptno and emp.sal >=t2.maxsal;


   
     select emp.* ,t2.maxsal from emp inner join
      (select max(sal) maxsal,deptno from emp group by deptno) t2
      on emp.deptno = t2.deptno where emp.sal >=t2.maxsal;


 ---需求：查询每个部门的人员数量
     select dept.deptno,dept.dname,t2.total from dept
       inner join (select count(ename) total,deptno from emp group by deptno )t2
       on dept.deptno = t2.deptno ;


   ?注解：在from字句中使用子查询时，必须给子查询使用别名


--需求：查询不在NEW YORK工作的员工信息(需要用多值连接符in)
select * from emp where deptno ！= (select deptno from dept where loc='NEW YORK');
select * from emp where deptno != (select deptno from dept where loc='NEW YORK');
--*拓展：in 和 notin 都是多值连接符，忽略空值。
--*拓展：with as 语句
with 
           temp1 as (select ... from 表1 where ...),
           temp2 as (select ... from 表2 where ...)
select temp1.inmoney/temp2.outmoney from temp1,temp2
where temp1.in_mon=temp2.out_mon;           
            
           


--*拓展:
--select * from emp where rownum = N ，当N大于1时是错的，因为rownum是依存于它的上一个rownum存在的，所以必须用 rownum <= N