oracle分页查询sql语句

–oracle分页查询sql语句

select a.empno, 
  a.ename, 
  a.job, 
  a.mgr, 
  a.hiredate, 
  a.sal, 
  a.comm, 
  a.deptno    
 from emp a where a.ename = 'SMITH' ;

–1、无ORDER BY排序的写法。(效率最高)
SELECT *

  FROM (SELECT ROWNUM AS rowno, t.* 

          FROM emp t

         WHERE t.ename = 'SMITH' /* hire_date BETWEEN TO_DATE ('20060501', 'yyyymmdd')

                             AND TO_DATE ('20060731', 'yyyymmdd')*/

           AND ROWNUM <= 20) table_alias

 WHERE table_alias.rowno >= 10;

–2.有ORDER BY排序的写法。(效率较高)
SELECT *

  FROM (SELECT tt.*, ROWNUM AS rowno

          FROM (  SELECT t.*

                    FROM emp t

                   WHERE hire_date BETWEEN TO_DATE ('20060501', 'yyyymmdd')

                                       AND TO_DATE ('20060731', 'yyyymmdd')

                ORDER BY create_time DESC, emp_no) tt

         WHERE ROWNUM <= 20) table_alias

 WHERE table_alias.rowno >= 10;

–5.另类语法。(有ORDER BY写法）
WITH partdata AS

     (

        SELECT ROWNUM AS rowno, tt.*

          FROM (  SELECT *

                    FROM k_task t

                   WHERE flight_date BETWEEN TO_DATE ('20060501', 'yyyymmdd')

                                         AND TO_DATE ('20060531', 'yyyymmdd')

                ORDER BY fact_up_time, flight_no) tt

         WHERE ROWNUM <= 20)

SELECT *

  FROM partdata

 WHERE rowno >= 10;