【oracle资料整理】--【7】子查询，层次查询，TOP 前几行，分页查询

子查询
    在select语句中嵌套了另一个select语句
     1)where 子句中嵌套子查询
     2)用子查询的结果 作为字段来出现

--1)where 子句中嵌套子查询,执行顺序是
      先执行子查询 再执行主查询
  找出工资高于公司平均工资的所有员工??
   select * from emp where sal+nvl(comm,0) > (select avg(sal+nvl(comm,0)) from emp);

   高于部门30中员工最高工资的其他员工???
    
  select * from emp where  sal+nvl(comm,0) > all (select sal+nvl(comm,0) from emp
               where deptno = 30);

   低于部门30中员工工资的其他员工???
  select * from emp where  sal+nvl(comm,0) < all (select sal+nvl(comm,0) from emp
               where deptno = 30);

  select * from emp where  sal+nvl(comm,0) < any (select sal+nvl(comm,0) from emp
               where deptno = 30);

--2）用子查询的结果 作为字段来出现 
      先执行主查询 再执行子查询

  <1>找员工姓名和直接上级的名字
   select ename as 员工姓名,(select ename from emp where empno = a.mgr) as 经理姓名
   from emp a;      
 <2>显示部门名称和人数
  select dname,(select count(*) from emp where deptno=a.deptno) as rs from dept a;
 <3>显示每个部门的最高工资的员工
    select * from emp a where (deptno, sal) in  (  select deptno,max(sal) from emp group by deptno);
 
 select a.* from emp a,(  select deptno,max(sal) as msal from emp group by deptno) c where a.deptno = c.deptno and
a.sal = c.msal;

--最大值和最小值的比较 转化为人数的比较
select * from emp a where (select count(*) from
 emp where deptno = a.deptno and
 sal > a.sal) = 0 and a.deptno is not null;

 <4>显示每个部门的工资前2名的员工
select * from emp a where (select count(*) from
 emp where deptno = a.deptno and
 sal > a.sal) <=1 and a.deptno is not null;

<6> 层次查询
--level 伪列 层次查询的时候可以使用的  层的编号

  select lpad('+',level,' ')||ename from emp
     connect by prior empno = mgr --父子关系 父结点中的empno = 子节点中的mgr
     start with mgr is null;--从 mgr is null的节点 开始遍历

select lpad('+',level,' ')||ename from emp
     connect by prior empno = mgr
     start with ename = 'BLAKE';

<7> TOP 前几行 (sqlserver)
    rownum  (oracle伪列)

 --emp表的前2行
 --rownum必须使用<=的关系比较运算符

 select * from emp where rownum <= 2;

 select top 2 * from emp; --sqlserver的写法

 select * from emp where rownum = 1;

 --公司工资最高的2个人
 /*select * from emp
 where rownum <= 2
order by sal desc;*/ 错误的

   select * from (select * from emp order by sal desc)
   where rownum <= 2;

 --分页查询
  --每页显示4行 一共有14行记录

  第1页    第1-4行
  第2页    第5-8行
  第3页    第9-12行
  第4页    第13-14行

 --希望每次页面显示的时候 都只查询回需要的记录
 
   select * from (select rownum as num,emp.* from emp)
   where num >= 9 and num <= 12;

   select * from (select rownum as num,emp.* from emp)
   where num >= 13 and num <= 14;