Use SQL statements to paging different databases with High Performance

In the process of program development, processing paging is a frequent event, because the software is basically linked 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 efficiency

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 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: 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 Select * from components where id not In (select ID from components where Rownum <= (pagesize * (CURRENTPAGE-1 ))) And rownum <= pagesize order by ID; For example: 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. Select * from components where rownum <= (Pagesize * (CURRENTPAGE-1) minus Select * from components where rownum <= (Pagesize * (CURRENTPAGE-2 )); Example: Select * from components where 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. Select * from (select rownum tid, components. * From components where rownum <= 100) Where TID <= 10;