MySQL和Oracle分页规律及应用

一、分页一般都会传入页码<pageIndex>和每页显示条数<pageSize>让其分页

1、MySql:

     采用limit关键字，eg：limit 0，5，0代表从哪一条数据开始，mysql数据条数从0开始算起，5代表显示几条数据。

     select * from tableName limit 0,5; --前5条数据

     规律： select * from tableName limit pageSize*(pageIndex-1),pageSize

2、oracle：

       采用rownum关键字,rownum从1开始，且单用rownum支持<=,不支持>=(亦说仅支持>=1),所以想用rownum分页需使用嵌套查询语 

       句。

        

        规律：pageSize*pageIndex-(pageSize-1),pageSize*pageIndex【pageIndex,pageSize】

        

select SCHEDULELCHID, SCHEDULEDATE, LCHID, OPERATOR, ACCEPTTIME, ASSESSORID, ASSESSTIME, SENDTIME, SLCHSTATE, PROCESSID, ACTID, INITTIME, UPDATESTATE, TRANSSTATE, VERSION, XMLCONTENT, ISLOCKED, LOCKEDOPERATOR
from (
 select s.*,rownum rn from (
 select SMGTWBD.TWBD_SCHEDULELCHREL.* from SMGTWBD.TWBD_SCHEDULELCHREL
  INNER JOIN SMGTWBD.TWBD_LCH_PLAYITEM ON SMGTWBD.TWBD_LCH_PLAYITEM.SCHEDULELCHID=SMGTWBD.TWBD_SCHEDULELCHREL.SCHEDULELCHID
 INNER JOIN SMGTWBD.TWBD_ACT ON SMGTWBD.TWBD_ACT.ACTID=SMGTWBD.TWBD_SCHEDULELCHREL.ACTID
 WHERE (SMGTWBD.TWBD_ACT.ACTSTATE IS not NULL) AND (SMGTWBD.TWBD_ACT.ACTSTATE='FINISH')
 AND (SMGTWBD.TWBD_LCH_PLAYITEM.HANDLESTATE IS NOT NULL) AND (SMGTWBD.TWBD_LCH_PLAYITEM.HANDLESTATE=1)
 order by SMGTWBD.TWBD_SCHEDULELCHREL.SCHEDULEDATE desc)
  s where rownum<=18 and SCHEDULEDATE >= '2015-11-23' )
  WHERE ( SCHEDULEDATE >= '2015-11-23' ) and rn>=16