不同数据库oracle mysql SQL Server DB2 infomix sybase分页查询语句

在不同数据库中的使用的分页查询语句:

当前页:currentpage
页大小:pagesize


1. Oracle数据库

 select * from (select A.*,rownum rn from ( QUERY_SQL ) A )  where rn <= ((currentpage+1)*pagesize) and rn > (currentpage*pagesize)

注:QUERY_SQL为查询sql语句。

select * from (select rownum rn,id from TABLENAME  where rownum <=((currentpage+1)*pagesize) ) A  where A.rn >= (currentpage*pagesize)

2. Infomix数据库

select skip currentpage first pagesize * from TABLENAME
 
3. DB2数据库

 select * from (select 字段1,字段2,字段3,rownumber() over(order by 排序用的列名 asc) as RN from 表名) as A1 where A1.RN between (currentpage*pagesize) and ((currentpage+1)*pagesize) 
 或
 select * from (select rownumber() over(order by id asc ) as rowid from table where rowid <=((currentpage+1)*pagesize) )   where rowid > (currentpage*pagesize)
 
 
4. SQL Server数据库

 select top pagesize *
  from TABLENAME
 where COLLUMN_NO not in
       (select top currentpage*pagesize COLLUMN_NO from TABLENAME order by COLLUMN_NO)
 order by COLLUMN_NO
 
5. Sybase数据库

Sybase 12.5.3版本支持top查询,或使用set rowcount N查询头N条数据
另外采用临时表:
select rowid=identity(12), column_name into #TEMPTABLE from TABLENAME
select column_name  from #TEMPTABLE where rowid >(currentpage*pagesize) and rowid < (currentpage*pagesize+pagesize)

6. MySQL数据库

 SELECT * FROM TABLE1 LIMIT (currentpage*pagesize),pagesize

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值