Paging query SQL

Time:2020-7-26

 

Common paging query SQL

First introduce an interview question, query the 200-300 data in the table. The application is a pagination query. Firstly, the data is marked by sub query, for example, Oracle is marked by rownum, and then the data of an interval is taken.

1、 Oracle

Keyword rownum

Rules:

Select * from (select a. *, rownum RC from table name where rownum < = endRow) a where a.rc > = startRow

Or select*
  FROM (SELECT A.*, ROWNUM RN
          FROM (SELECT * FROM TABLE_NAME) A
         WHERE ROWNUM <= 40)
 WHERE RN > 20

 

Among themSELECT * FROM TABLE_NAMERepresents the SQL query statement without paging, while rownum and RN limit the range of query data.

example:

   select * from ( select OL.*, rownum  rc from OffsiteOutreachTeam_List OL where rownum <= 48 ) OL where OL.rc >= 9      

   SELECT * FROM (SELECT A.*, ROWNUM RN FROM (SELECT * FROM OffsiteOutreachTeam_List) A WHERE ROWNUM <= 40) WHERE RN > 20      

  select * from ( select OL.*, rownum rc from OffsiteOutreachTeam_List OL) where rc >= 9 and rc <= 20

  select * from ( select OL.*, rownum rc from OffsiteOutreachTeam_List OL) where rc between 10 and 20

Note: you can avoid duplicate data on different pages by sorting.

  1.   SELECT * FROM  
  2.   2  (  
  3.   3  SELECT A.*, ROWNUM RN  
  4.   4  FROM (SELECT ID, OWNER, OBJECT_NAME FROM TEST WHERE OWNER IS NOT NULL ORDER BY OWNER, ID) A  
  5.   5  WHERE ROWNUM <= 20  
  6.   6  )  
  7.   7  WHERE RN >= 11;

 

2、 Mysql

Keyword: limit

Mysql database page select * from table name limit startRow, PageSize (PageSize is the number of records displayed on each page)

3、 SQL Server

Keywords: Top

3. SQL Server 2000 database page select top PageSize * from table name where column name not in (select top PageSize * page column name from table name order by column name) order by column name

4. SQL Server 2005 database page select * from (select column name, row_ Number() over (order by column name 1) as alias from table name) as t where T. column name1 > = startRow and t. column name1 < = endRow

 

,

Common paging query SQL

First introduce an interview question, query the 200-300 data in the table. The application is a pagination query. Firstly, the data is marked by sub query, for example, Oracle is marked by rownum, and then the data of an interval is taken.

1、 Oracle

Keyword rownum

Rules:

Select * from (select a. *, rownum RC from table name where rownum < = endRow) a where a.rc > = startRow

Or select*
  FROM (SELECT A.*, ROWNUM RN
          FROM (SELECT * FROM TABLE_NAME) A
         WHERE ROWNUM <= 40)
 WHERE RN > 20

 

Among themSELECT * FROM TABLE_NAMERepresents the SQL query statement without paging, while rownum and RN limit the range of query data.

example:

   select * from ( select OL.*, rownum  rc from OffsiteOutreachTeam_List OL where rownum <= 48 ) OL where OL.rc >= 9      

   SELECT * FROM (SELECT A.*, ROWNUM RN FROM (SELECT * FROM OffsiteOutreachTeam_List) A WHERE ROWNUM <= 40) WHERE RN > 20      

  select * from ( select OL.*, rownum rc from OffsiteOutreachTeam_List OL) where rc >= 9 and rc <= 20

  select * from ( select OL.*, rownum rc from OffsiteOutreachTeam_List OL) where rc between 10 and 20

Note: you can avoid duplicate data on different pages by sorting.

  1.   SELECT * FROM  
  2.   2  (  
  3.   3  SELECT A.*, ROWNUM RN  
  4.   4  FROM (SELECT ID, OWNER, OBJECT_NAME FROM TEST WHERE OWNER IS NOT NULL ORDER BY OWNER, ID) A  
  5.   5  WHERE ROWNUM <= 20  
  6.   6  )  
  7.   7  WHERE RN >= 11;

 

2、 Mysql

Keyword: limit

Mysql database page select * from table name limit startRow, PageSize (PageSize is the number of records displayed on each page)

3、 SQL Server

Keywords: Top

3. SQL Server 2000 database page select top PageSize * from table name where column name not in (select top PageSize * page column name from table name order by column name) order by column name

4. SQL Server 2005 database page select * from (select column name, row_ Number() over (order by column name 1) as alias from table name) as t where T. column name1 > = startRow and t. column name1 < = endRow