三种数据库sql语句分页

三种数据库sql语句分页

https://wenku.baidu.com/view/e3a079886f1aff00bfd51e00.html

 

MySQL

从哪条开始---结束

select * from 表名 limit 1,4 ;//从第1条开始，取4条

每页显示3条，显示第一页 limit 0,3

每页............................第二页 limit 3,3

每页............................第三页 limit 6,3

每页.............................第8页 limit (页数-1)*页大小, 每页大小);

 

SQL2000

第一页: select top 3 * from dep;

第二页: select top 3 * from dep where depid not in ( select top 3 depid from dep )

第三页: select top 3 * from dep where depid not in (select top 6 depid from dep);

第8页: select top 3 * from dep where depid not in (select top (8-1)*3 depid from dep);//(8-1)*3 sql 2000 只能写数字 sql 2005 写表达式

 

Oracle

select * from (select rownum r1, t.* from dept t where rownum<=6) where r1>=4

第一页

select rownum r1, t.* from dept t where rownum<=3

第二页

select * from (select rownum r1, t.* from dept t where rownum<=6) where r1>=4

 

第三页

select * from (select rownum r1, t.* from dept t where rownum<=9) where r1>=7

 

第8页

select * from (select rownum r1, t.* from dept t where rownum<=3*8) where r1>=8*3-2

 

select * from (select rownum r1, t.* from dept t where rownum<=3*8) where r1>=8*3-(3-1)

 

select * from (select rownum r1,t.* from dept t where rownum<=n*3) where r1>=n*3-(3-1)

 

 

 

 

My sql

1 select * from dept limit 0,3; 从第0条开始 取3条

2 select * from dept limit 3,3;

3 select * from dept limit 6,3;

select *from dept limit (n-1)*3,3;

 

Sql 2005

1 select top 3 * from dept ;

2 select top 3 * from dept where did not in (select top 3 did from dept);

3 select top 3 * from dept where did not in (select top 6 did from dept);

select top 3 * from dept where did not in (select top (n-1)*3) did from dept;

 

Oracle

select * from dept where rownum<=3

select * from (select * from dept where rownum <=6) where rownum >=4

select * from (select * from dept where rownum <=9) where rownum>=7

select * from (select * from dept where rownum <=12) where rownum>=10

select * from (select * from dept where rownum <=n*3) where rownum>=n*3-(3-1)

select * from (select rownum r1,t.* from dept where rownum <=n*3) where rownum>=(n-1)*3+1