单表分页
================
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 <=${rows}
</where>
)
<where>
RN>=${firstRowIndex}
</where>
</select>
=======
>= 可写成 >
<= 可写成 <
不写< xml解析可能会报错