In the process of program development, processing paging is a frequent event, because the software is basically connected to the database. However, efficiency is what we are pursuing. If we select all the records that meet the conditions as before, and then perform paging processing, A lot of system processing time will be wasted. To be able
In the process of program development, processing paging is a frequent event, because the software is basically connected to the database. However, efficiency is what we are pursuing. If we select all the records that meet the conditions as before, and then perform paging processing, A lot of system processing time will be wasted. To be able
In the process of program development, processing paging is a frequent event, because the software is basically connected to the database. However, efficiency is what we are pursuing. If we select all the records that meet the conditions as before, and then perform paging processing, A lot of system processing time will be wasted. To improve the efficiency, we only need to select the data we need to reduce the processing time of the database. The following are commonly used SQL paging processing:
1. SQL Server and Access Database
Microsoft databases are all family members and basic operations are similar. The following paging statements are often used:
PAGESIZE: number of records displayed per page
CURRENTPAGE: Current page number
The data table name is components.
The index primary key is: id
The following is a reference clip:
Select top PAGESIZE * from components where id not in
(Select top (PAGESIZE * (CURRENTPAGE-1 ))
Id from components order by id) order by id |
For example:
The following is a reference clip:
Select top 10 * from components where id not in
(Select top 10*10 id from components order by id)
Order by id |
Select from 101 records and select only the first 10 records
2. Oracle Database
Because the Oracle database does not have the Top keyword, it cannot be operated like Microsoft Data. There are two methods:
(1) the opposite is used.
PAGESIZE: number of records displayed per page
CURRENTPAGE: Current page number
The data table name is components.
The index primary key is: id
The following is a reference clip:
Select * from components where id not
In (select id from components where
Rownum <= (PAGESIZE * (CURRENTPAGE-1 )))
And rownum <= PAGESIZE order by id; |
For example:
The following is a reference clip:
Select * from components where id not in
(Select id from components where rownum <= 100)
And rownum <= 10 order by id; |
Select the first 10 records from 101 to record.
(2) Use minus, that is, Chinese means minus.
The following is a reference clip:
Select * from components where rownum
<= (PAGESIZE * (CURRENTPAGE-1) minus
Select * from components where rownum
<= (PAGESIZE * (CURRENTPAGE-2 )); |
Example: select * from components where
The following is a reference clip:
Rownum <= 10 minus select * from components
Where rownum <= 5 ;. |
(3) The Oracle rownum is used, which is the sequence number automatically returned by Oracle queries. It is generally not displayed, but can be seen through select rownum from [Table name]. Note, it is the total number of records from 1 to the current.
The following is a reference clip:
Select * from (select rownum tid, components.
* From components where rownum <= 100) where tid <= 10; |