分页查询 rownum --分页查询 --查询投TOP N --在oracle查询中,默认有一个隐藏的列"rownum",这个列可以对查询到的数据进行排序 select p.*,rownum from libprivilege p; --执行下面两条sql,理解rownum与数据的关系 select p.*,rownum from libprivilege p where pid<1000000; --rownum是对order(排序)前的数据建立对应对应关系的 select p.*,rownum from libprivilege p where pid<1000000 order by pname,rownum; --查询前10条的语句,使用rownum进行控制 select p.* from libprivilege p where rownum<=3*10; --要求对权限表数据进行分页查询,每页要求查询10条数据,现在要求查出第3页的数据 --分页查询的原理:1.先查询出前N页的数据;2.再减去N-1页的数据 select b.* from (select p.*,rownum rn from libprivilege p where rownum<=1*10) b where rn >(1-1)*10; --注意:每执行一次查询都会产生一个rownum select b.*,rownum rn2 from (select p.*,rownum rn from libprivilege p where rownum<=1*10 ) b where rn >(1-1)*10; select b.*,rownum rn2 from (select p.*,rownum rn from libprivilege p where rownum<=1*10 order by rownum desc ) b where rn >(1-1)*10; --注意:使用了分页查询后,如果需要进行排序查询,需要理解rownum与数据的关系 --练习: --分页查询权限表第一页数据(每页查询5条),并按照pid进行排序 select p.*,rownum from libprivilege p where rownum<=1*5 order by pid; --分页查询权限表第一页数据(每页查询5条),并按照pname进行排序 -- select p.*,rownum from libprivilege p where rownum<=1*5 order by pname,rownum;--错误,这是对查询结果的排序 --select p.*,rownum from libprivilege p where rownum<=1*5 order by pname; --错误 select p.*,rownum rn from libprivilege p order by pname --查询的是整表的数据,排序是对整个表数据的排序 --在带排序的分页查询中,需要进行先排序后分页,否则排序的关系会乱 select * from (select p.*,rownum rn from libprivilege p order by pname) r where rownum <=2*5; --三种分页查询: --第一种:带order排序的分页查询,需要执行三次查询 --1.第一次查询时排序查询 --2.第二次查询时查找出前N页数据 --3.第三次查询时才查找出第N页数据 --rownum在第一次查询中 select p.*,rownum rn from libprivilege p order by pname; select * from (select * from (select p.*,rownum rn from libprivilege p order by pname) r where rn <=4*10) where rn>(4-1)*10; --rownum在第二次查询中 select * from (select r.*,rownum rn from (select p.* from libprivilege p order by pname) r where rownum <=2*10) where rn>(2-1)*10; --分页查询的原理:1.先查询出前N页的数据;2.再减去N-1页的数据 --第二种:不带order排序使用的分页查询 select b.* from (select p.*,rownum rn from libprivilege p where rownum<=1*10) b where rn >(1-1)*10 ; select p.*,rownum rn from libprivilege p; --练习: --1.检查没有排序时,分页后的数据和查询所有的数据,数据是否一致 --2.检查带有排序时,分页后的数据与排序后所有的数据,数据是否一致 --第三种:不带order排序使用的分页查询 select b.* from (select p.*,rownum rn from libprivilege p where rownum<=2*10) b where rn >(2-1)*10 ; --这条sql会查询整表数据,在数据量很大的表中,查询的效率低 select b.* from (select p.*,rownum rn from libprivilege p) b where rn<=2*10 and rn >(2-1)*10 ; --这条sql会查找多一次全表,在数据量很大的表中,查询的效率低 select * from (select r.*,rownum rn from (select p.* from libprivilege p order by pname) r ) where rn<=2*10 and rn>(2-1)*10; 分页查询 <select id="findTotal" resultType="int"> select count(t.id) c from goods t left outer join category c on t.category=c.id </select> <!-- <![CDATA[<=]]> 或使用 <= --> <select id="findList" parameterType="Pagger" resultType="Goods"> select * from (select rownum rn,t.*,c.id as "cateInfo.id",c.cname as "cateInfo.cname" from goods t left outer join category c on t.category=c.id where rownum <= #{endIndex}) where rn>${startIndex} </select> select * from GOODS t --总页数--统计查询 select count(id) total from goods select count(*) from goods; --带排序的分页 --需要先排序后查询在分页--这样才是对整个表的数据做排序, --如果先查询后排序,则是对查询结果做排序 --第一步,先排序 select g.*,c.id as "cateInfo.id",c.cname as "cateInfo.cname" from goods g left outer join category c on g.category=c.id order by g.name --第二步,把排序后的数据查询出前N也 select rownum rn,k.* from (select g.*,c.id as "cateInfo.id",c.cname as "cateInfo.cname" from goods g left outer join category c on g.category=c.id order by g.name) k where rownum<=(2*5) --第三步,把前N页数据进行二次查询,查找出第N也 select * from (select rownum rn,k.* from( select g.*,c.id as "cateInfo.id",c.cname as "cateInfo.cname" from goods g left outer join category c on g.category=c.id order by g.name) k where rownum<=(3*5)) where rn>5 --没有排序的分页 select * from (select rownum rn,t.*,c.id as "cateInfo.id",c.cname as "cateInfo.cname" from goods t left outer join category c on t.category=c.id where rownum <=20) where rn>10 -- select count(t.id) c from goods t left outer join category c on t.category=c.id select * from (select rownum rn,t.*,c.id as "cateInfo.id",c.cname as "cateInfo.cname" from goods t left outer join category c on t.category=c.id where rownum <= #{endIndex}) where rn>${startIndex} select rownum,id,name,price,store,gtype,smell,uptime,downtime,category,color from goods where rownum<=10 select * from (select rownum rn,t.*,c.id as "cateInfo.id",c.name as "cateInfo.cname" from goods t left outer join category c on t.category=c.id) where rownum <=(2*5) where rownum<=1