Oracle, DB2, SQL Server, Mysql, Access paging SQL statements

Source: Internet
Author: User

Recently summarized the database paging SQL usually used in the project. You can post more efficient SQL statements with paging.
SQL Server Paging
The first method of paging
Parameters to be required:
PageSize How many data are displayed per page
PageNumber pages coming from the client
Total number of records in the Totalrecouds table select COUNT (*) from table name
TotalPages Total Pages
Totalpages=totalrecouds%pagesize==0?totalrecouds/pagesize:totalrecouds/pagesize+1
Pages to calculate the previous pages bar data
pages= pagesize* (pageNumber-1)
SQL statements:
Select Top PageSize * FROM table name where ID not in (select top pages ID from table name ORDER by ID) Order by ID
The second method of paging
PageSize How many data are displayed per page
PageNumber pages coming from the client
pages=pagesize* (pageNumber-1) +1
Select Top PageSize * from table name where id>= (select Max (ID) from (select top pages ID from table name ORDER BY ID ASC) T)

MySQL paging  
  Required parameters:  
 pagesize shows how many data   per page;
 pagenumber pages   from the client;
Total number of records in  totalrecouds table select COUNT (*) from table name  
 totalpages total pages  
 totalpages= totalrecouds%pagesize==0?totalrecouds/pagesize:totalrecouds/pagesize+1 
 pages start position  
 pages= pagesize* (pageNumber-1)  
 sql statement:  
 select * from table name limit pages, pageSize;  
 mysql paging depends on the keyword limit it requires two parameters: start position and pagesize 
  Start position = page Size * (pages 1)  
  Start position = pagesize* (pageNumber-1)

Oracle Paging
PageSize How many data are displayed per page
PageNumber pages coming from the client
Total number of records in the Totalrecouds table select COUNT (*) from table name
TotalPages Total Pages
Totalpages=totalrecouds%pagesize==0?totalrecouds/pagesize:totalrecouds/pagesize+1
StartPage Start position
Startpage= pagesize* (pageNumber-1) +1
Endpage=startpage+pagesize
SQL statements
Select A.* from
(
Select RowNum num, t.* from table name t where column = some value order by ID ASC
) A
where A.num>=startpage and A.num<endpage

DB2 Sub-page
int startpage=1//Start Page
int endpage; End Page
int pagesize=5; Page size
int pagenumber=1//Request page

Startpage= (pageNumber-1) *pagesize+1
Endpage= (startpage+pagesize);


SQL statements
SELECT * FROM (select field 1, Field 2, Field 3, Field 4, Field 5,rownumber () over (Order by sort field ASC) as rowID from table name) as a where A.rowid >= StartPage and A.rowid <endpage

Access Paging
PageSize How many data are displayed per page
PageNumber pages coming from the client
pages=pagesize* (pageNumber-1) +1
SQL statements
Select Top PageSize * from table name where id>= (select Max (ID) from (select top pages ID from table name ORDER BY ID ASC) T)


Warm tip: Every time you reprint, embodies the meaning of my writing this article!!! Please indicate the source http://www.blogjava.net/sxyx2008/when reprinted. Thank you for your cooperation!!!

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.