mysql替换Oracle中的ROWNUM

最新推荐文章于 2026-06-21 09:53:41 发布

原创最新推荐文章于 2026-06-21 09:53:41 发布 · 2.6k 阅读

1 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#mysql替换Oracle中的ROWNU #mysql分页 #Rownum

收录于

本文介绍了如何在MySQL中使用特定的查询方式来替代Oracle中的ROWNUM进行分页操作。通过示例展示了Oracle的ROWNUM分页查询语法，并提供了对应的MySQL查询转换实例。

<sql id="pageS">
select c.* from (
select b.* from
(select @rownum :=@rownum + 1 AS rm,a.* from
(SELECT @rownum := 0) r,

(

</sql>

<sql id="pageE">
) a ) b where b.rm <= #{page}) c where c.rm >= #{page}

</sql>

oracle:

SELECT *
FROM (SELECT M.*, ROWNUM RN
FROM (
select ques_name,ques_type,ques_options,ques_score,ques_serial,QUES_ID,QUES_ANSWER,QUES_EXPLAIN
from q_question_tab
where ques_id in (SELECT ques_id FROM q_paper_question where paper_id=#{paperId})
and ques_type=#{ques.quesType} order by ques_serial
) M
WHERE ROWNUM <= #{page}) z
WHERE RN >= #{page}

mysql:

<include refid="pageS" />

select ques_name,ques_type,ques_options,ques_score,ques_serial,QUES_ID,QUES_ANSWER,QUES_EXPLAIN
from q_question_tab
where ques_id in (SELECT ques_id FROM q_paper_question where paper_id=#{paperId})
and ques_type=#{ques.quesType} order by ques_serial

<include refid="pageE" />