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!!!