Orcle基本语句（五）分页查询

--分页查询---begin
--sqlserver top
--mysql limit
--oracle rownum(伪列),oracle中伪列(rownum,rowid)

 

--查询工资为前五的信息
SELECT ROWNUM,tmp.* FROM (SELECT * FROM emp WHERE SAL IS NOT NULL ORDER BY sal DESC) tmp;
--查询工资在第6条至第12条的信息
--方法一：
--1.查询在前12（包含第12条）的信息
SELECT sal FROM (SELECT * FROM emp WHERE SAL IS NOT NULL ORDER BY sal DESC) WHERE ROWNUM <=12;
--2.查询在前6（包含第六条）的信息
SELECT sal FROM (SELECT * FROM emp WHERE SAL IS NOT NULL ORDER BY sal DESC) WHERE ROWNUM <=5;
--3.
SELECT ROWNUM, tmp.*
  FROM (SELECT * FROM emp WHERE sal IS NOT NULL ORDER BY sal DESC) tmp
 WHERE sal IN
       (SELECT sal
          FROM (SELECT * FROM emp WHERE sal IS NOT NULL ORDER BY sal DESC)
         WHERE rownum <= 12)
   AND sal NOT IN
       (SELECT sal
          FROM (SELECT * FROM emp WHERE sal IS NOT NULL ORDER BY sal DESC)
         WHERE rownum <= 5);
--方法二：嵌套子查询
SELECT tem.*
  FROM (SELECT rownum r, tmp.*
          FROM (SELECT * FROM emp WHERE sal IS NOT NULL ORDER BY sal DESC) tmp) tem
 WHERE tem.r BETWEEN 6 AND 12;

--查询工资最高的第6条到第12条的员工信息 
--1.
SELECT *
  FROM (SELECT rownum row_top ,tt.*
          FROM (SELECT e.*
                  FROM employees e
                 WHERE e.salary IS NOT NULL
                 ORDER BY salary DESC) tt)
 WHERE row_top BETWEEN 6 AND 12;
--2. 效率较于1
SELECT *
  FROM (SELECT rownum row_top, tt.*
          FROM (SELECT rownum rn, e.*
                  FROM employees e
                 WHERE e.salary IS NOT NULL
                 ORDER BY salary DESC) tt)
 WHERE row_top <= 12
   AND row_top >= 6;
--3.效率较于2---推荐使用
SELECT *
  FROM (SELECT rownum rn, tt.*
          FROM (SELECT *
                  FROM employees e
                 WHERE e.salary IS NOT NULL
                 ORDER BY salary DESC) tt
         WHERE rownum <= 12)
 WHERE rn >= 6

 

--视图
--查询全部的20号 部门的雇员信息（雇员编号，姓名，工作，雇佣日期）
CREATE OR REPLACE VIEW view20 AS SELECT empno, ename, job, hiredate FROM emp WHERE deptno = 20;  
--创建视图v_dept：查询每个部门的编号，名称，员工人数，员工工资总和
CREATE OR REPLACE VIEW v_dept AS 
SELECT e.deptno,d.dname,COUNT(e.empno) c, SUM(e.sal) s
  FROM emp e
  FULL JOIN dept d
    ON e.deptno = d.deptno
 GROUP BY e.deptno,d.dname;

SELECT * FROM v_dept WHERE c >= 3;
SELECT Count(*) from emp;