Introduction to access database paging method

Since there are many database records, the query results need to be displayed in pages. The following are two paging methods for the access database.

(Learning video sharing: Introduction to Programming)

1. Access query result paging method 1

1. Method

Suppose the number of records per page is CntPerPage. To obtain the query results on page Page, use the following SQL statement.

SELECT * FROM (
SELECT TOP CntPerPage * FROM(
SELECT TOP Page*CntPerPage * FROM TableName WHERE Query_Conditionm ORDER BY OrderField
) ORDER BY OrderField DESC
) ORDER BY OrderField

TableName is the table name, Query_Condition is the query condition, and OrderField is the field name used for sorting

2. Principle

Nested three-step query:
First step query: Query the records of the current page and all previous pages, and sort them in descending order by the sort field

SELECT TOP Page*CntPerPage * FROM TableName WHERE Query_Conditionm ORDER BY OrderField

The second step query: Query the previous CntPerPage of the record just queried (already arranged in descending order) Query out the records (keep them in descending order). These are all the records on Page

SELECT TOP CntPerPage * FROM( 第一步查询结果 ) ORDER BY OrderField DESC

The third step of query: The previous query has actually queried all the records on Page, but in descending order. Arranged, this third step is just to put the data just obtained into a positive sequence. If the order is not required, or there are other data sorting processes in the program, then this step can be omitted.

SELECT * FROM ( 第二步查询结果 ) ORDER BY OrderField

3. Example

A Student table, you want to query all records whose age is less than 20 years old, sort by name, the number of records per page is 100, you want to get the query on page 5 As a result, use the following SQL statement.

SELECT * FROM (SELECT TOP 100 * FROM( SELECT TOP 500 * FROM Student  WHERE age<20 ORDER BY name ) ORDER BY name DESC) ORDER BY name

2. NOT IN method commonly used on the Internet

When searching for paging methods on the Internet, this is the most common method.
Method:

SELECT TOP CntPerPage * FROM TableName WHERE Query_Conditionm AND id NOT IN (
SELECT TOP (Page-1)*CntPerPage id FROM TableName WHERE Query_Conditionm ORDER BY id
) ORDER BY id

Principle:
Two-step query:
First step: Select all eligible record IDs before this page

SELECT TOP (Page-1)*CntPerPage id FROM TableName WHERE Query_Conditionm ORDER BY id

Second Step: Add the query condition "id is not in the query results of the ids just selected" and get the first CntPerPage records. In this way, the query results will filter all the data on the first Page-1 page, and then select the data on Page-1. All records.

SELECT TOP CntPerPage * FROM TableName WHERE Query_Conditionm AND id NOT IN (第一步查询结果) ORDER BY id

3. Paging query efficiency

The actual test efficiency of the two methods is almost the same, but I personally think that the efficiency of the NOT IN judgment in the second method should not be too high, so I feel that if the amount of records is relatively large, the first method should be more efficient.

Related recommendations: access database tutorial

The above is the detailed content of Introduction to access database paging method. For more information, please follow other related articles on the PHP Chinese website!