程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle數據庫基礎 >> ORACLE分頁查詢SQL語法

ORACLE分頁查詢SQL語法

編輯:Oracle數據庫基礎
--1:無ORDER BY排序的寫法。(效率最高) ­

--(經過測試,此方法成本最低,只嵌套一層,速度最快!即使查詢的數據量再大,也幾乎不受影響,速度依然!) ­

SELECT * ­

  FROM (Select ROWNUM AS ROWNO, T.* ­

           from k_task T ­

          where Flight_date between to_date('20060501', 'yyyymmdd') and ­

                to_date('20060731', 'yyyymmdd') ­

            AND ROWNUM <= 20) TABLE_ALIAS &shy;

WHERE TABLE_ALIAS.ROWNO >= 10; &shy;

&shy;

--2:有ORDER BY排序的寫法。(效率最高) &shy;

--(經過測試,此方法隨著查詢范圍的擴大,速度也會越來越慢哦!) &shy;

SELECT * &shy;

  FROM (SELECT TT.*, ROWNUM AS ROWNO &shy;

           FROM (Select t.* &shy;

                    from k_task T &shy;

                   where flight_date between to_date('20060501', 'yyyymmdd') and &shy;

                         to_date('20060531', 'yyyymmdd') &shy;

                   ORDER BY FACT_UP_TIME, flight_no) TT &shy;

          WHERE ROWNUM <= 20) TABLE_ALIAS &shy;

where TABLE_ALIAS.rowno >= 10; &shy;

&shy;

--3:無ORDER BY排序的寫法。(建議使用方法1代替) &shy;

--(此方法隨著查詢數據量的擴張,速度會越來越慢哦!) &shy;

SELECT * &shy;

  FROM (Select ROWNUM AS ROWNO, T.* &shy;

           from k_task T &shy;

          where Flight_date between to_date('20060501', 'yyyymmdd') and &shy;

                to_date('20060731', 'yyyymmdd')) TABLE_ALIAS &shy;

WHERE TABLE_ALIAS.ROWNO <= 20 &shy;

   AND TABLE_ALIAS.ROWNO >= 10; &shy;

--TABLE_ALIAS.ROWNO  between 10 and 100; &shy;

&shy;

--4:有ORDER BY排序的寫法.(建議使用方法2代替) &shy;

--(此方法隨著查詢范圍的擴大,速度會越來越慢哦!) &shy;

SELECT * &shy;

  FROM (SELECT TT.*, ROWNUM AS ROWNO &shy;

           FROM (Select * &shy;

                    from k_task T &shy;

                   where flight_date between to_date('20060501', 'yyyymmdd') and &shy;

                         to_date('20060531', 'yyyymmdd') &shy;

                   ORDER BY FACT_UP_TIME, flight_no) TT) TABLE_ALIAS &shy;

where TABLE_ALIAS.rowno BETWEEN 10 AND 20; &shy;

&shy;

&shy;

--5另類語法。(有ORDER BY寫法) &shy;

--(語法風格與傳統的SQL語法不同,不方便閱讀與理解,為規范與統一標准,不推薦使用。) &shy;

With partdata as( &shy;

  SELECT ROWNUM AS ROWNO, TT.*  FROM (Select * &shy;

                  from k_task T &shy;

                 where flight_date between to_date('20060501', 'yyyymmdd') and &shy;

                       to_date('20060531', 'yyyymmdd') &shy;

                 ORDER BY FACT_UP_TIME, flight_no) TT &shy;

   WHERE ROWNUM <= 20) &shy;

    Select * from partdata where rowno >= 10; &shy;

&shy;

--6另類語法 。(無ORDER BY寫法) &shy;

With partdata as( &shy;

  Select ROWNUM AS ROWNO, T.* &shy;

    From K_task T &shy;

   where Flight_date between to_date('20060501', 'yyyymmdd') and &shy;

         To_date('20060531', 'yyyymmdd') &shy;

     AND ROWNUM <= 20) &shy;

    Select * from partdata where Rowno >= 10;   &shy;
  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved