常见数据库的分页语句

1、oracle
select * from (select a.*,rownum row_num from (select * from jobs t where job_desc like '%a%' order by t.job_id desc) a) b where b.row_num>=1 and b.row_num<=5;
2、mysql
select * from jobs where job_desc like '%a%' order by t.job_id desc limit 1,5;
3、db2
select * from (select rownumber() over(order by t.job_id desc) as rowNum,t.* from jobs as t where job_desc like '%a%' order by t.job_id desc) as res where res.rowNum between 1 and 5;
4、sqlserver2005
select * from (select *,row_number() over(order by job_id desc) as rowNum from jobs where job_desc like '%a%')as t where t.rowNum between 1 and 5;
5、sqlserver2000
select top 5 * from jobs where jobs_id not in (select top 5 jobs_id from jobs order by jobs_id desc) where job_desc like '%a%' order by jobs_id desc desc;
6、access
select top 5 * from jobs where jobs_id not in (select top 5 jobs_id from jobs order by jobs_id desc) where job_desc like '%a%' order by jobs_id desc desc;
注意acess没有top 0
7、postgre
select * from jobs limit 10 offset 0;
offset 偏移量 0表示从第一条开始
limit 共取几条 10表示本次查询10条