Implementation methods of segmented query technology in major databases

Implementation of database segmented query technology

---

Segmented query means that when querying data, not all the data that meets the conditions are read into the memory at once. Querying all the data at once will reduce the query efficiency, and the response to the client will take a long time. Segmented query means that according to the size of the current page, the result of each query is the number displayed on each page, which is greatly improved. Query efficiency

For example, there are 100,000 pieces of data in the database, 20 pieces per page. When clicking on the page number, only the 20 pieces of content that need to be displayed on the page number are queried, and no other pages are queried, that is, only 20 pieces of data are queried each time.

SQL Server

Fetch N records starting from the Mth record in the database table, using the Top keyword: Note that if there is both top and order by in the Select statement, it is selected from the sorted result set :

SELECT *

FROM ( SELECT Top N *
FROM (SELECT Top (M + N - 1) * FROM 表名称 Order by 主键 desc) t1 ) t2
Order by 主键 asc

For example, whether to retrieve 10 records or 20 records from the table Sys_option (the primary key is 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

Start from the Mth record in the database table To retrieve N records

SELECT *
FROM (SELECT ROWNUM r,t1.* From 表名称 t1 where rownum < M + N) t2
where t2.r >= M

For example, whether to retrieve 10 records or 20 records from the table Sys_option (the primary key is sys_id), the statement is as follows:

SELECT *
FROM (SELECT ROWNUM R,t1.* From Sys_option where rownum < 30 ) t2
Where t2.R >= 10

MySQL database

My sql database is the simplest, using the LIMIT function of mysql ,LIMIT [offset,] rows The statement to retrieve N records starting from M records in the database table is:

The following is the code fragment:

SELECT * FROM 表名称 LIMIT M,N

For example, from the table Sys_option (the primary key is sys_id), from 10 records or To retrieve 20 records, the statement is as follows:

The following is a code snippet:

select * from sys_option limit 10,20

When I used ORACLE in the past, I just used ROWNUM as the condition for paging. Regardless of the efficiency, it is easier to implement anyway.

So how does ACCESS implement the paging function? After reading some information, it is a bit complicated and requires writing a lot of code. I thought of a good method to share with you, which is a SQL statement:

select top 15 * from YOURTABLE where ID not in (select top 15 ID from YOURTABLE order by ID desc) order by ID desc

Explain a little, 15 is the number of rows per page, YOURTABLE is the table name, ID is the primary key field in the table (of course you can also use other), my SQL statement above , is to take out the second page (second 15 records). If you want to change the page number, just change the following 15 into the corresponding multiple.

Using this method, combined with the DataRepeater plug-in without paging function, I wrote the notepad program in just three strokes, five divisions and two divisions. Don't laugh at me, I'm a newbie in .NET, I'll write this first to practice my skills.

　If anyone is interested, please leave a message and I can post the code.

Please note:

If you take the first 15 records, the SQL statement cannot be written like this:

　　select top 15  *  from YOURTABLE where ID not in(select top 0 ID from YOURTABLE order by ID desc)  order by ID desc

If you write it like this, an error will be reported. Write it directly:

select top 15  *  from YOURTABLE   order by ID desc

If you want to add restrictions, you can write it like this:

select top 15  *  from YOURTABLE where ID not in(select top 15 ID from YOURTABLE where  NAME='wallimn' order by ID desc) and NAME='wallimn'  order by ID desc