PHP MSSQL Database Paging SQL statement _php tips

When we are writing systems such as MIS systems and Web applications, are related to the interaction with the database, if the data in the database is very large, one retrieval of all the records, will occupy a large system of resources, so we often use, how much data will be taken from the database only how many records, that is, the use of pagination statements. According to the content of their own use, the common database SQL server,oracle and MySQL page statements, from the database table in the first m data to take N records of the statement summarized as follows:
SQL Server
Take the n record from the first m record in the database table, using the TOP keyword: Note If the SELECT statement has both top and order by, select from a 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
Instance:
SELECT * FROM (select top pagesize * FROM (select top Pagesize*cureentpage * to user_table ORDER by ID ASC) as Asysta BLE order by ID DESC) as bsystable ORDER by ID ASC
For example, from the table sys_option (primary key is sys_id) from 10 records or retrieve 20 records, the statement is as follows:
SELECT *
From (SELECT 20 *
From (SELECT the * from Sys_option ORDER BY sys_id desc) t1) T2
ORDER BY sys_id ASC
Oralce Database
Retrieving N records from the first m record in a database table
SELECT *
From (SELECT rownum r,t1.* from table name T1 where RowNum < M + N) T2
where T2.R >= M
For example, from the table sys_option (primary key is sys_id) from 10 records or retrieve 20 records, the statement is as follows:
SELECT *
From (SELECT rownum r,t1.* from sys_option where RowNum <) T2
Where T2. R >= 10
MySQL Database
The simplest of my SQL database is to use the LIMIT function of MySQL, LIMIT [offset,] rows to retrieve N records from the M record in the database table as:
SELECT * FROM table name LIMIT m,n
For example, from the table sys_option (primary key is sys_id) from 10 records or retrieve 20 records, the statement is as follows:
SELECT * FROM Sys_option limit 10,20