不同數據庫OracleMySQLSQLServerDB2infomixsybase分頁查詢語句

不同數據庫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

本文永久更新鏈接地址：

聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com