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 ;
结果