Oracle vs. mysql and SQL Server paging

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