Oracle查詢100萬到200萬之間的資料

取100-150條資料的方法

1. 最佳選擇：利用分析函式

        row_number() over ( partition by col1 order by col2 )                               

比如想取出100-150條記錄,按照tname排序    

select tname,tabtype from (                               

            select tname,tabtype,row_number() over ( order by tname ) rn from tab                

         ) where rn between 100 and 150;   從第100條（包含第100條）到第150條資料，總共51條資料

2. 使用rownum 虛列

        select tname,tabtype from (                    

            select tname,tabtype,rownum rn from tab where rownum <= 150                  

         ) where rn >= 100;

查詢100萬到200萬之間的資料：

select ID,
       CASEID,
       PERSONID,
       NAME,
       TYPE,
       STATE,
       ADDRESS,
       PHONENO,
       CONTACT,
       DRIVERLICENSEDOCID,
       DRIVERLICENSEISSUEOFFICE,
       SEX,
       AGE,
       WOUNDINFOID,
       DRIVINGAGE,
       DRIVERLICENSETYPEID,
       CONVEYANCEMEANSID,
       OUTINGPURPOSE,
       ISDRIVER,
       ISWEARINGSEATBELT,
       UPDATETIME,
       UPDATEACCOUNTID,
       PARTYUNIT,
       PERSONAREACODE,
       INJUREDPARTID,
       PERSONALTYPEID,
       ACCIDENTREASONID,
       BUSSINESSCERTIFICATION,
       CORPORATION,
       ESCAPETIME,
       BIRTHDAY,
       CORPERATIONSEX,
       CORPERATIONAGE,
       CORPERATIONADDRESS,
       CORPERATIONCONTACT,
       CORPERATIONIDNUMBER,
       LICENCE,
       MEMO,
       BCISSUER
  from (select ID,
               CASEID,
               PERSONID,
               NAME,
               TYPE,
               STATE,
               ADDRESS,
               PHONENO,
               CONTACT,
               DRIVERLICENSEDOCID,
               DRIVERLICENSEISSUEOFFICE,
               SEX,
               AGE,
               WOUNDINFOID,
               DRIVINGAGE,
               DRIVERLICENSETYPEID,
               CONVEYANCEMEANSID,
               OUTINGPURPOSE,
               ISDRIVER,
               ISWEARINGSEATBELT,
               UPDATETIME,
               UPDATEACCOUNTID,
               PARTYUNIT,
               PERSONAREACODE,
               INJUREDPARTID,
               PERSONALTYPEID,
               ACCIDENTREASONID,
               BUSSINESSCERTIFICATION,
               CORPORATION,
               ESCAPETIME,
               BIRTHDAY,
               CORPERATIONSEX,
               CORPERATIONAGE,
               CORPERATIONADDRESS,
               CORPERATIONCONTACT,
               CORPERATIONIDNUMBER,
               LICENCE,
               MEMO,
               BCISSUER,
               row_number() over(order by id) rn
          from TC_PARTY
         order by id)
 where rn between 1000001 and 2000000

從1000001到2000000，總共100萬條資料