大家好,又见面了,我是全栈君,祝每个程序员都可以多学几门语言。
在不同数据库中的使用的分页查询语句:
当前页: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
发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/118272.html原文链接:https://javaforall.cn
【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛
【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...