Paging implementation solution for common databases

With the development of databases, today's databases can store a large amount of data, and the memory is getting bigger and bigger. However, no matter how large your memory is, the memory is always insufficient, and paging is required, detailed database paging operations will be provided below:
 
1. Oracle:
SQL code
Select * from (select row _. *, rownum _ from (query_ SQL) row _ where rownum = <max) where rownum _> = min
 
2. SQL Server:
SQL code
Select top @ pagesize * from tablename where id not in (select top @ pagesize * (@ page-1) id from tablename order by id) order by id
 
 
3. MySQL
 
SQL code
Select * from tablename limit position, counter
 
 
4. DB2
 
SQL code
Select * from (select *, rownumber () as ROW_NEXT from tablename) where ROW_NEXT between min and max
 
 
Paging solution 1: (using Not In and select top pages)
 
SQL code
Select top 10 * FROM TestTable WHERE (id not in (select top 20 id FROM TestTable ORDERBY id )) orderbyid select top page size * FROM TestTable WHERE (id not in (select top page size-1 * Number of pages to be queried-1 id FROM Table ORDERBY id) ORDERBYID
 
Idea: first query the IDs of all the items before the page to be queried. The query IDs are not among the specified number of items.
 
Paging solution 2: (using the ID greater than the number and select top pages) the most efficient
 
SQL code
Select top 10 * FROM TestTable WHERE (ID> (select max (id) FROM (SELECT TOP20 id FROM TestTable ORDERBYid) as t )) orderby id select top page size * FROM TestTable WHERE (ID> (select max (id) FROM (select top page size * Number of pages to be queried-1 id FROM Table ORDERBY id) as t) ORDERBY ID
 
Idea: first obtain all the previous number IDs of the pages to be queried, and obtain the largest idnumber among them. Use this largest idnumber as a sign to find a specified number larger than this id number.
 
 
Paging solution 3:
SQL code
Select top PageSize * FROM (select top nPage * PageSize * from YOURTABLE order by id) as a order by id desc select top number of entries per page * FROM (select top page to be queried * number of entries per page) * from YOURTABLE order by id) as a order by id desc
 
Train of Thought: first query the total number of items before the page to be queried (including the current page), and then sort them in reverse order, taking the specified number of items

This article is from "zys08"