The paging function is our basic skill.Must be firmly grasped.By organizing the paging algorithm,Hope to be more comfortable with the paging function,

Whether it is the implementation of pagination query code in drp or the investigation of pagination query seen in interview questionsAll give me a hint:pagination query is important.It must be considered when the amount of data is large.I haven't taken the time to stop before summing it up.Now I have looked at the content of the paging query in the oracle video again.Find it easy to understand.

Paging algorithm

When I first looked up information online,Seeing a lot of paging content,It feels a lot messy.This is not the case.Those materials on the Internet are similar.The problem was with myself.I didn't understand the premise of pagination?We all know that these variables are involved whenever there is pagination:how many records per page (pagesize), current page (pagenow), total records (totalrecords), total pages (totalpages), startpage (beginrow), end page ( endrow). Those data paging algorithms on the Internet are useful for pagesize, beginpage and endpage. In fact, these variables need to be classified:I divided them intoThree categories:

a. Need to query from the database:totalrecords. "select count (*) from tablename"

b. The most basic needs of the user:pagesize and pagenow. (I personally think this is the premise of the paging algorithm)

c. Calculated from other variables:totalpages, beginrow, and endrow. (It is necessary to calculate beginrow and endrow here because they are used in paging queries.totalpages is the information the page needs to provide). Specific calculation formula:

totalpages:if ((totalrecords%pagesize) == 0) {
           } else {
             totalpages=totalrecords/pagesize + 1;
beginrow:(pagenow-1) * pagesize +1
endrow:pagenow * pagesize

In this way, the values ​​of these variables can be obtained.How to use it, please see the second and third parts.

Commonly used paging method in oracle

In fact, whether it is oracle or sqlserver, the basis for implementing paging queries is subqueries.In my own words:select in a set of select.

There are three ways to oracle paging.I will only talk about one that is easy to understand here.Take the employee table (emp) as an example. Suppose there are 10 records. Now pagination requires 5 records per page, and the current page is 2. The query results are records 6-10. Let's do it with specific numbers,Then change to a variable.

oracle first step:select a. *, rownum rn from (select * from emp) a;where rownum is the internally assigned row number.Select * from emp in brackets is to query all the records in the emp table.Then we use the query result as a view to further query.The outer selection adds a rownum in addition to querying all of emp for later queries.

oracle to implement the second step:select a. *, rownum rn from (select * from emp) a where rownum<= 10;The second step adds a condition to query for records with a row number less than or equal to 10. There may be such a question here why not write rownum>= 6 and rownum<= 10 directly. The problem is not solved.Oracle internal mechanism does not support this writing.

oracle to implement the third step:select * from (select a. *, rownum rn from (select * from emp) a where rownum<= 10) where rn>= 6;ok, so you can complete querying 6-10 records.

At last. We convert to variables.May be in a java program or pl/sql.

Three more need to be converted:"emp "is the specific table name," 6 "is (pagenow-1) * pagesize +1, and" 10 "is pagenow * pagesize.

This method can be used as a template,Easy to modify.All changes only need to change the innermost layer.For example, to query the specified column:modify the innermost layer to select ename, sal from emp;sort according to the salary column:select ename, sal from emp order by sal;all only need to modify the innermost layer.

Common paging methods in 3.sqlserver

We still use the example of the employee table to explain the implementation of paging in sqlserver

Use of the first top:

The first step of sqlserver implementation:select top 10 * from emp order by empid;Remove the first 10 records.

sqlserver implements the second step:select top 5 * from (select top 10 * from emp order by empid) a order by empid desc. The 10 records retrieved are sorted in descending order by employee number and then 5 records are retrieved. The first time here is sorted in ascending order,Sorting the second time in descending order is clever.I did not expect top to play such an effect.Here 10 is replaced by the variable pagenow * pagesize and 5 is replaced by pagesize.

The use of the second top and in:select top 5 * from emp where empid in (select top 10 empid from emp order by empid) order by empid desc;Here the position of 10 is replaced by the variable pagenow * pagesize and 5 is replaced by pagesize.

Other queries are similar.I won't repeat them here.

  • Previous ASP regular expression method to clear HTML specified tags
  • Next ASPNET colorful drop-down box development examples