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


阅读更多

没有更多推荐了,返回首页