How to query a record in Mysql in which page of paging

Preface

In practice, we may encounter such a problem: we know the id of a certain record, and then we need to determine which page this record is on if it is sorted and paginated by id. Today's article provides you with an idea.

Let's take a look at the detailed implementation method.

Query the paging position by ID

To query the paging position based on ID, for example, if the IDs are sorted in reverse order, the following SQL can be used to query the number of records greater than this ID:

select count(id) from user where id > 5;

In the example, user is the table name and the 5-digit id to be matched. Obviously, since it is sorted in reverse order, you only need to find the number of records greater than this id. If it is sorted in ascending order, you only need to find the number of records less than this id.

After finding the count value, how do you calculate which page the current record is on? Here is a Java code example:

int pageSize = 10;
// Assume that the result found above is count, and the value is 11
int count = 11;
// Calculate the pageNum of the current record
// Get the current page number as page 2 by taking the modulus and adding 1 int pageNum = count/pageSize + 1;
// If you want to further obtain a certain position on a certain page, you can take the remainder, that is, the first record on the second page (starting from 0)
int index = count%pageSize;

Multi-dimensional sorting and positioning

The above sorting by simple ID is relatively easy to solve. What if the dimension of sorting a record is not just ID? For example, first sort by age in reverse order. If the age is the same, then follow up with ID and sort in reverse order. The basic SQL statements are as follows:

select id, age from user order by age desc,id desc;

At this point, we know that there is a record with an id of 5 and an age of 18. How do we determine the position of this record in the multi-condition sort?

First, the difficulty of multi-condition sorting lies in the case where the ages are the same. If the ages are different, the following SQL can be used to locate the position of the id, just like "Querying the paging position based on ID":

select count(id) from user where age > 18;

In this way, you can query the position of this record when the combined sorting age is different. The algorithm for the specific position is the same as the first case.

So how to deal with the situation when age is repeated? Of course, this can be achieved through complex association queries or sub-table queries. Here is another way, which is to query the number of records with the same age and an id greater than the current user:

select count(id) from user where age = 18 and id > 5;

The above obtains the records with the same age and id greater than 5. Add the statistical results of the first and second steps. The question is whether we have returned to the simple mode of "querying the paging position based on ID", or the same algorithm can calculate which page the current record is on.

Although this solution queries the database twice, if the index is well established, it is more convenient, concise and efficient than associated queries or subqueries.

summary

The above are two dimensions of thinking when encountering similar problems in practice. I hope they can bring you a breakthrough, and I also hope that you can provide better solutions.

Well, the above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support of 123WORDPRESS.COM.