Assuming that this is page pageno, there are pagesize records per page, and now the student table is queried using MySQL, Oracle, and SQL Server paging.
1. mysql Paging query:
1 SELECT2 FROM4 student5 LIMIT (PageNo-1) * pagesize,pagesize;
Understanding: (Limit n,m) + starts from the nth line to fetch M Records, n from 0 to calculate.
2, Oracel paged query:
1 Select 2 * 3 from 4 (5 SELECT 6 ROWNUM RN, * 7 from 8 student 9 WHERE10 ROWNUM <= p Ageno * pageSize11 ) WHERE13 rn > (pageNo-1) * pageSize
Understanding: Suppose PageNo = 1,pagesize = 10, first take the row number less than or equal to 10 from the student table, and then remove the RN greater than 0 records from these records, so as to achieve paging purposes. RowNum starting from 1.
3. SQL Server Paging query:
1 Select 2 TOP PageSize * 3 from 4 (5 SELECT 6 row_number () up (ORDER by ID ASC) RowNumber, * 7 FR OM 8 Student 9 ) A10 WHERE11 a.rownumber > (PageNo-1) * PageSize
Understanding: Suppose PageNo = 1,pagesize = 10, sort by the ID of the student table first, rownumber as the line number, and then take out 10 records starting at line 1th.
Paged query Some of the database may have several ways, here may not be the most efficient way to query, but this is the most convenient paging query I use, if interested can also be used in other paged query the way to study.
Another blog post:
1, MySQL with limit paging
/*
* sql: Can be a single-table query statement, or a multi-table union query statement
* FirstIndex: The index in fact
* PageSize: The number of records displayed per page
*/
Select o.* from (SQL) O limit firstindex,pagesize
Eg:select * FROM (SELECT * from Student) limit 0, 20; Fetch 1-20 Data
SELECT * FROM (SELECT * from Student) limit 20, 20; Fetch 21-40 Data
The second type of notation
SELECT * from Student where limit (currentPage-1) *pagesize, pageSize;
2, Oracle with rownum paging
/*
* FirstIndex: Start index
* PageSize: The number displayed per page
* sql: Can be a simple single-table query statement, or it can be a complex multi-table union query statement
*/
SELECT * FROM (select A.*,rownum rn from (SQL) a where rownum<= (firstindex+pagesize)) where Rn>firstindex
Eg:select * FROM (select A.*,rownum rn from (SELECT * from Student ORDER by ID ASC) a where rownum<=20) where rn>0 Fetch 1-20 Data
SELECT * FROM (select A.*,rownum rn from (SELECT * from Student ORDER by ID ASC) A where rownum<= (20+20)) where rn>20 Fetch 21-40 Data
The second type of notation
SELECT * FROM (select RowNum rn,t.* from Student t where rownum <= currentpage*pagesize) where rownum > (currentp age-1) *pagesize;
3. SQL Server with top paging
/*
* FirstIndex: Start index
* PageSize: The number displayed per page
* Ordercolumn: Sorted field Name
* sql: Can be a simple single-table query statement, or it can be a complex multi-table union query statement
*/
Select Top PageSize o.* from (select Row_number () over (order by Ordercolumn) as rownumber,* from (SQL)) as O where Rownumbe r>firstindex;
Eg:select Top o.* from (select Row_number () up (order by ID ASC) as rownumber,* from (SELECT * from Student)) as o where rownumber>0; Fetch 1-20 Data
The second type of notation
Select Top PageSize * from Student where ID not in (select Top (currentPage-1) *pagesize ID from Student);
Oracle vs. mysql and SQL Server paging