Different databases: oracle mysql SQL Server DB2 infomix sybase paging query statement, infomixsybase
Paging query statements used in different databases:
Current page: currentpage
Page size: pagesize
1. Oracle Database
Select * from (select A. *, rownum rn from (QUERY_ SQL) A) where rn <= (currentpage + 1) * pagesize) and rn> (currentpage * pagesize)
Note: QUERY_ SQL is a query SQL statement.
Or
Select * from (select rownum rn, id from TABLENAME where rownum <= (currentpage + 1) * pagesize) A where A. rn> = (currentpage * pagesize)
2. Infomix Database
Select skip currentpage first pagesize * from TABLENAME
3. DB2 database
Select * from (select Field 1, Field 2, Field 3, rownumber () over (column name asc used for order by sorting) as RN from table name) as A1 where A1.RN between (currentpage * pagesize) and (currentpage + 1) * pagesize)
Or
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 database
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 Database
Sybase 12.5.3 Supports top query or use set rowcount N to query the first N data entries.
Use temporary tables:
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 database
SELECT * FROM TABLE1 LIMIT (currentpage * pagesize), pagesize
Oracle, SQL _server, MySQL, DB2, Access, and Sybase6 databases are most suitable for the following situations:
SQL Server and MySQL are enough, except ASP and ASP. NET use the former, PHP and JSP use the latter.
For example, if you want to get started with oracle, SQL Server, DB2, sybase, and mySQL
Most large portal websites use the following databases:
Oracle db2 sybase
Building Complex Data Warehouses
Stable, professional maintenance, and easy data analysis
If a problem occurs, you can receive timely professional guidance or disaster recovery.
Small database websites are widely used
Mysql sqlserve
Small Size
Easy to learn and use