Oracle 使用 rownum 分页 原创

Oracle 使用 rownum 分页

复制收展SQLSELECT *
FROM (
SELECT TT.*, ROWNUM AS ROWNO
FROM (
select * from um_form b
) TT
WHERE ROWNUM <= 20
) TABLE_ALISA
WHERE TABLE_ALISA.ROWNO > 11;
1
2
3
4
5
6
7
8
9

遇到一些问题

复制收展Bash-- Oracle 使用 rownum 分页
SELECT *
FROM (
         select rowid as rid, rownum as num, b.*
         from um_form b
         order by FM_CLS asc
     ) t
where 1=1
  --and rownum>=11;  --ROWNUM不能使用大于,用了查不出数据。rownum是虚拟列，是得出结果后，再进行计算的,游标的指向必须从1开始。
  and t.num >= 11
  and t.num <= 12;

-- Oracle 使用 rownum 分页
SELECT *
FROM (
         SELECT TT.*, ROWNUM AS ROWNO
         FROM (
                  select rowid as rid, rownum as num, b.*
                  -- rowid as rid 原因，ORA-01446: 无法使用 DISTINCT, GROUP BY 等子句从视图中选择 ROWID 或采样
                  --select rowid ,rownum
                  from um_form b
                  order by FM_CLS asc  -- num顺序乱了
              ) TT
         WHERE ROWNUM <= 20 -- ROWNUM不能使用大于
     ) TABLE_ALISA
WHERE TABLE_ALISA.ROWNO > 11;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

Mybatis 分页，数据源为Oracle

收展XML<!-- 分页start -->
	<sql id="pageStart">
		<isNotNull property="page">
			SELECT * FROM (
			SELECT TT.*,ROWNUM AS ROWNO FROM (
		</isNotNull>
    </sql>
	<sql id="pageEnd">
		<isNotNull property="page">
			) TT
			WHERE ROWNUM <= #page.endRow#
			) TABLE_ALISA
			WHERE TABLE_ALISA.ROWNO > #page.startRow#
		</isNotNull>
    </sql>
    <!-- 分页end -->
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

使用

复制收展XML<select id="query" parameterClass="Aims.umAccScope" resultClass="Aims.umAccountView">
    <include refid="pageStart"/>
    select * from dual
	<include refid="pageEnd"/>
</select>
1
2
3
4
5