Oracle MYSQL DB2 sql分页



​​Oracle​​​ , ​​​MYSQL​​​ , ​​​DB2​​​ , ​​​sql​​​ , ​​​pagination​​


Oracle




  1. select * from (select rownum,name from user_tables where rownum <= endIndex)
  2. where rownum > startIndex






如果加了order就更麻烦了




  1. select * from (select rownum,name from user_tables order by orderstr) where rownum <= endIndex
  2. and rownum > startIndex






Mysql最简单

limit




  1. select   *   from   table  where whereclause order by orderstr limit  start,pageNum






DB2

使用rownumber over和fetch first rows only




  1. select * from (select syscat.indexes.*, rownumber() over() rownum from syscat.indexes where owner='DB2INST1' order by indname desc fetch first 100 rows only) where rownum < 20 and rownum >= 15










  1. DB2INST1        SQL
  2. DB2INST1        SQL100805143558190        DB2INST1        16
  3. DB2INST1        SQL100805143558150        DB2INST1        17
  4. DB2INST1        SQL100805143558100        DB2INST1        18
  5. DB2INST1        SQL100805143557870        DB2INST1        19