SQL語句履行次序詳解

SQL語句履行次序詳解。本站提示廣大學習愛好者：（SQL語句履行次序詳解）文章只能為提供參考，不一定能成為您想要的結果。以下是SQL語句履行次序詳解正文

本文實例匯總了Oracle完成分頁查詢的SQL語法，整頓給年夜家供年夜家參考之用，概況以下：

1.無ORDER BY排序的寫法。(效力最高)

經由測試，此辦法本錢最低，只嵌套一層，速度最快！即便查詢的數據量再年夜，也簡直不受影響，速度仍然！

sql語句以下：

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
WHERE TABLE_ALIAS.ROWNO >= 10;

2.有ORDER BY排序的寫法。(效力最高)

經由測試，此辦法跟著查詢規模的擴展，速度也會愈來愈慢！

sql語句以下：

SELECT *
 FROM (SELECT TT.*, ROWNUM AS ROWNO
      FROM (Select t.*
          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) TABLE_ALIAS
where TABLE_ALIAS.rowno >= 10;

3.無ORDER BY排序的寫法。(建議應用辦法1取代)

此辦法跟著查詢數據量的擴大，速度會愈來愈慢！

sql語句以下：

SELECT *
 FROM (Select ROWNUM AS ROWNO, T.*
      from k_task T
     where Flight_date between to_date('20060501', 'yyyymmdd') and
        to_date('20060731', 'yyyymmdd')) TABLE_ALIAS
WHERE TABLE_ALIAS.ROWNO <= 20
  AND TABLE_ALIAS.ROWNO >= 10;
TABLE_ALIAS.ROWNO between 10 and 100;

4.有ORDER BY排序的寫法.(建議應用辦法2取代)

此辦法跟著查詢規模的擴展，速度也會愈來愈慢！

sql語句以下：

SELECT *
 FROM (SELECT TT.*, ROWNUM AS ROWNO
      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) TABLE_ALIAS
where TABLE_ALIAS.rowno BETWEEN 10 AND 20;

5.另類語法。(有ORDER BY寫法）

該語法作風與傳統的SQL語法分歧，不便利浏覽與懂得，為標准與同一尺度，不推舉應用。此處貼出代碼供年夜家參考之用。

sql語句以下：

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;

6.另類語法 。(無ORDER BY寫法）

With partdata as(
 Select ROWNUM AS ROWNO, T.*
  From K_task T
  where Flight_date between to_date('20060501', 'yyyymmdd') and
     To_date('20060531', 'yyyymmdd')
   AND ROWNUM <= 20)
  Select * from partdata where Rowno >= 10; 

信任本文所述代碼可以或許對年夜家有必定的參考自創價值。