SQL Server, MySQL, and Oracle paging query SQL statements

Solutions for querying paging data using SQL Server SQL statements:

You must select 3,000th records on page 1 of tbllendlist, with 100 records on each page. -------------------------

----------
Method 1:
----------
Select Top 100 * From tbllendlist
Where fldserialno not in
(
Select top 300100 fldserialno from tbllendlist
Order by fldserialno
)
Order by fldserialno

----------
Method 2:
----------
Select Top 100 *
From tbllendlist
Where (fldserialno>
(Select max (fldserialno)
From (select top 300100 fldserialno
From tbllendlist
Order by fldserialno) as t ))
Order by fldserialno

Method 1: Fast execution speed!

However, this practice is still very troublesome. We strongly expect Microsoft to invent a new paging SQL statement !!!!

 

SQL Server ---------------------------------------------------------------------------------
Obtain N records from the M records in the database table. Use the top Keyword: Note that if the SELECT statement contains both top and order by, select from the sorted result set:
Select *
From (select Top N *
From (select top (m + n-1) * from table name order by primary key DESC) T1) T2
Order by primary key ASC

For example, to retrieve 10 or 20 records from the table sys_option (primary key: sys_id), the statement is as follows:
Select *
From (select top 20 *
From (select top 29 * From sys_option order by sys_id DESC) T1) T2
Order by sys_id ASC

Oralce database --------------------------------------------------------------------------------
Retrieve N records from the M records in the database table
Select *
From (select rownum R, T1. * from table name T1 where rownum <m + n) T2
Where t2.r> = m
For example, to retrieve 10 or 20 records from the table sys_option (primary key: sys_id), the statement is as follows:
Select *
From (select rownum R, T1. * From sys_option where rownum <30) T2
Where t2.r> = 10
If you are not familiar with Oracle database paging, this page will be followed by a dedicated explanation of Oracle paging technology.

My SQL Database Connector ---------------------------------------------------------------------------------------------
The simplest MySQL database is to use MySQL's limit function, limit [offset,] rows statement to retrieve N records from M records in the database table:
Select * from table name limit m, n

For example, to retrieve 10 or 20 records from the table sys_option (primary key: sys_id), the statement is as follows:
Select * From sys_option limit 10, 20