oracle分页 效率的分析

select t2.*,rn  from 
(
       select sysuser.*, rownum rn from 
       
       --在这里自定义需要分页的表
       --sysuser
       (select * from sysuser) sysuser
       
       
       where rownum<=20
) t2
where t2.rn > 10;

oracle的分页需要传递起始和结束的下标

逐步分解【取20-30条的数据】

1、现根据结束下标查询数据

select * from sysuser where rownum <= 30;

2、根据开始下标查询数据【在1中的查询结果集中，再添加rownum】

【select sysuser.*, rownum index from sysuser where rownum <= 30】作为一张表，再进行查询

select t2.*, rn from 
(
	select sysuser.*, rownum rn from sysuser where rownum <= 30
) t2	--as关键字会报错
where t_page.index >=20;

		select t2.*, rn from (
			select xxxx.*, rownum rn from (
				xxxx
			) where xxxx.rownum <= 20
		) t2 where t2.rn >10

		select t2.*, rn from (
			select sysuser.*, rownum rn from (
				select * from (
		  			select 
		  				 sysuser.id,
				         sysuser.userid, 
				         sysuser.username, 
				         sysuser.groupid, 
				         sysuser.sysid,
				         sysuser.userstate, 
				         decode(sysuser.groupid, 
				             '1', (select mc from userjd where id = sysuser.sysid), 
				             '2', (select mc from userjd where id = sysuser.sysid),
				             '3', (select mc from useryy where id = sysuser.sysid),
				             '4', (select mc from usergys where id = sysuser.sysid)) sysmc
		  			from sysuser) sysuser
			) where sysuser.rownum <= 20
		) t2 where t2.rn >10