第一种方式:
select * from (select rownum r,* from 表名 where Rownum <= 结束记录数) where r >={开始记录数} 效率还可以 33W数据测试
示例:
SELECT * FROM (select ROWNUM R,*
from A LEFT OUTER JOIN T B B ON A.A = B.B) C WHERE C.A >= TO_DATE('2020/07/12', 'yyyy/mm/dd hh24:mi:ss')
AND C.A <= TO_DATE('2020/07/19', 'yyyy/mm/dd hh24:mi:ss') AND C.A = '' AND C.A = '' and ROWNUM <=10 ORDER BY C.A DESC) WHERE R >= 0
第二方式:
select * from (select rownum r,* from 表名 ) where r between {(page - 1) * rows} and {page * rows} page:页数 rows:页行数
SELECT * FROM (select ROWNUM R,*
from A LEFT OUTER JOIN T B B ON A.A = B.B) C WHERE C.A >= TO_DATE('2020/07/12', 'yyyy/mm/dd hh24:mi:ss')
AND C.A <= TO_DATE('2020/07/19', 'yyyy/mm/dd hh24:mi:ss') AND C.A = '' AND C.A = '' ORDER BY C.A DESC) WHERE r between R >= 0 on ROWNUM <=10
解析:
TO_DATE是日期格式的转换函数
利用ROWNUM进行分页的示例
1、如果想判断一个字段是否包括在一个字符串中,用in 关键字
示例:数据字段 in ('字段1','字段2','字段3')