SQL database paging query statement

MySQL

-- Query 10th to 20 data records

The code is as follows:	Copy code
SELECT * FROM table_name WHERE... LIMIT 10, 20;

 

SQLServer

The code is as follows:	Copy code
-- Query the first 10 | 10% data entries Select top 10 | 10% * FROM table_name; -- Query 10th to 20 data records Select top 10 * FROM (Select top 20 * FROM table_name order by id ASC) Order by id DESC; -- Use ROW_NUMBER () to query 10th to 20 pieces of data by page SELECT * FROM (SELECT *, ROW_NUMBER () OVER (order by id) AS 'rownumber' FROM table_name) WHERE a. RowNumber BETWEEN 10 AND 20;

Oracle

The code is as follows:	Copy code
-- Query the first 10 data records SELECT * FROM table_name where rownum <= 10; -- Query 10th to 20 data records SELECT * FROM (Select rownum rn, a. * FROM table_name a where rownum <= 20) WHERE rn> 10;

 

DB2

The code is as follows:	Copy code
-- Query 10th to 20 data records SELECT * FROM (SELECT *, ROWNUMBER () OVER (order by id ASC) AS rn from table_name) AS a1 WHERE a1.rn BETWEEN 10 AND 20;

 

PostgreSQL

The code is as follows:	Copy code
-- Query 10th to 20 data records SELECT * FROM table_name LIMIT 10 OFFSET 10; -- Offset 10 indicates that the value starts from 10th. -- Limit: a total of 10 items are obtained, which indicates 10 items are queried this time.

 

Informix

The code is as follows:	Copy code
-- Query 10th to 20 data records Select skip 10 FIRST 10 * FROM table_name order by id; -- SKIP n, SKIP n -- FIRST n: obtain the FIRST n records.

 

Hibernate

The code is as follows:	Copy code
Query query = session. createQuery ("from BeanName "); // Query 10th to 20 data records Query. setFirstResult (10 ); Query. setMaxResults (10 ); List = query. list ();

Attached to a database paging query table

Database	Paging query statement	Description
MySql	"QUERY_ SQLLimit ?,? "	Use the limit keyword, the first "? "Is the starting line number, The second "? "Is the number of returned entries
Oracle	SELECT * FROM (Select a. *, ROWNUM RN FROM  (QUERY_ SQL) A WHERE ROWNUM <= ?) Where rn> =?	Using the rownum keyword and nested layer-3 select Statement implementation. First "? "Indicates the end row number, The second "? "Indicates the actual row number.
SQL Server	No general statements	You can use top n to return the first n records or use stored procedures.
DB2	Suppose the query statement: select t1. * from t1 order By t1.id; the paging statement can be: "Select * from (select rownumber () over (Order by t1.id) as row _, t1. * from t1 Order by t1.id) as temp _ where row _ Between? + 1 and? "	Two "? "Between records
InterBase	"QUERY_ SQLRow? To ?"	Two "? "Between records
PostgreSQL	"QUERY_ SQLLimit? Offset ?"	First "? "Is the starting line number, the second "? "Representative Number of returned Records