Shulou(Shulou.com)06/01 Report--

Preface

In practice, we will encounter the problem of knowing the id of a record, and then we need to determine which page the record is on if it is sorted and paged according to id. Today's article provides you with an idea.

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

Query paging location based on ID

To query the paging position according to ID, for example, in reverse order of ID, you can query the number of records greater than this ID by the following SQL:

Select count (id) from user where id > 5

In the example, user is the table name and 5 id to be matched. Obviously, because it is in reverse order, you only need to find the number of records larger than this id, and if it is in positive order, it is less than this id.

When the count value is found, how to calculate the page on which the current record is located? here is an example of the java code:

Int pageSize = 10 count%pageSize / suppose the result found above is count, the value is 11int count = 11 count%pageSize / calculate the pageNum// of the current record by taking the module and adding 1 to get the current number of pages on page 2 int pageNum = count/pageSize + 1 / if you want to further get a position on a page, you can do the remainder, that is, the first record on page 2 (starting from 0)

Multi-dimensional sorting and positioning

It is easy to sort through a simple ID, so if the dimension of sorting a record is not only ID, for example, it is sorted in reverse order by age (age). If the age is the same, then follow ID to sort in reverse order. The basic sql statement is as follows:

Select id, age from user order by age desc,id desc

At this point, we know that a record whose id is 5 and the age is 18, how to determine the position of this record in multi-conditional sorting.

First of all, the difficulty of multi-conditional sorting lies in the situation where the age is the same. If the age is different, you can locate the location of the id just like "query the paging position by ID" with the following sql:

Select count (id) from user where age > 18

In this way, we can query the location of this record when the combined sorting age is different, and the algorithm for the specific location is the same as in the first case.

So what happens when age uses repetition. Of course, this can be achieved through complex associated queries or subtable queries. Here, another way is to query that the age is the same and the id is greater than the number of records of the current user:

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

The above obtained the records with the same age and id greater than 5, adding the statistical results of the first step and the second step, the question is whether it is back to the simple mode of "paging position according to ID query", or the same algorithm can calculate which page the current record is located on.

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

Summary

The above are the two thinking dimensions of similar problems encountered in practice, hoping to bring you a breakthrough, but also hope that you can provide a better plan.

All right, that's all of this article. I hope the content of this article has a certain reference and learning value for everyone's study or work. If you have any questions, you can leave a message and exchange. Thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.