Mysql与Oracel的分页查询

Mysql分页查询limit m,n

select t.* from test t
limit m, n
//m代表从那一行开始
//n代表查几条数据

查询表bbs_color中的第1-5条数据

SELECT
bbs_color.id,
bbs_color.`name`,
bbs_color.parent_id,
bbs_color.img_url
FROM
bbs_color
LIMIT 0, 5

结果

查询表bbs_color中的第6-10条数据

SELECT
bbs_color.id,
bbs_color.`name`,
bbs_color.parent_id,
bbs_color.img_url
FROM
bbs_color
LIMIT 5, 5

结果

Oracle分页查询rownum

• 不能对rownum 使用 >（大于或等于1的数值）、>=（大于1的数值）、=（大于1的数值），否则无结果
• rownum >10 没有记录，因为第一条不满足去掉的话，第二条的rownum又成了1，所以永远没有满足条件的记录

多条语句查询

这种查询语句效率较高,内层控制最大值,外层控制最小值.
- 查询poi表的第1-5条数据

select rownum rn ,t.* from poi t where rownum <= 5;
select t2.* from (select rownum rn ,t.* from poi t) t2 where rn <= 5;--前5条

结果

• 查询poi表的第6-10条数据

select t3.* from (select rownum rn2,t2.* from (select rownum rn,t.* from poi t) t2 where rn <= 10) t3 where rn2 > 5; --6到10条
select t3.* from (select t2.* from (select rownum rn,t.* from poi t) t2 where rn <= 10) t3 where rn > 5; --6到10条
select t2.* from (select rownum rn,t.* from poi t where rownum <=10) t2 where rn > 5;
select t2.* from (select rownum rn,t.* from poi t where rownum <=10) t2 where rn >= 6;

结果

between m and n

select rownum rn,t.* from poi t where rownum between 1 and 5 ;
select t2.* from (select rownum rn,t.* from poi t) t2 where t2.rn between 1 and 5 ;
select t2.* from (select rownum rn,t.* from poi t) t2 where t2.rn between 6 and 10 ;

结果