ORACLE分页 博客分类： SQL sqloracle分页

单表分页

================

  SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM trz_project) A
)
WHERE RN BETWEEN 11 AND 20

 

多表链接 左链接

==================

 SELECT * FROM
(
SELECT A.* , ROWNUM RN
FROM (select
   p.projectid,
   t.cqjy_content
   FROM TRZ_PROJECT P LEFT OUTER JOIN TRZ_TRADE T  ON P.PROJECTID=T.PROJECTID) A
 )WHERE RN BETWEEN 11 AND 20   

 

比第一个分页效率高的写法

=============

 

SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
WHERE ROWNUM <= 40
)
WHERE RN >= 21

 ===================

 

  SELECT * FROM
(
SELECT A.* , ROWNUM RN
FROM (select
   p.projectid,
   t.cqjy_content
   FROM TRZ_PROJECT P LEFT OUTER JOIN TRZ_TRADE T  ON P.PROJECTID=T.PROJECTID order by t.createtime desc) A
     WHERE ROWNUM<=10
 )WHERE RN>=1

 

 

Mybatis 分页

 

<select id="queryByDealInput" parameterType="Map" resultType="Map">
   SELECT * FROM
  (
  SELECT A.* , ROWNUM RN
  FROM (select
     <include refid="tradeColumnSql"/>,
     <include refid="projectColumnSql"/>
     FROM TRZ_PROJECT P LEFT OUTER JOIN TRZ_TRADE T  ON P.PROJECTID=T.PROJECTID) A
      <where>
      ROWNUM &lt;=${rows}
      </where>  
   )
   <where>
   RN>=${firstRowIndex}
   
  </where>  
  
 </select>

 =======

>= 可写成 &gt;

<= 可写成 &lt;

不写&lt  xml解析可能会报错