oracle database: paging rownum

1. Paging introduction

Why do you use paging? Because sometimes the list content is too much and you need to use paging to display it. If there is too much data, a single page cannot display all the content, so only a part of the data is displayed each time.
Pagination is a technique that displays all data in segments to users. What the user sees each time is not all of the data, but a part of it. If you don't find the content you want in it, you can convert the visible content by specifying the page number or turning the page until you find the content you want. In fact, this is very similar to how we read books.

There are two solutions to achieve classification

  1. Query all the records in the database at once, and then display the specified records on each page.
  2. Query the database multiple times, and only get the data on this page each time and display it.

Nowadays, the data in website construction is massive. If you follow the plan 1, it will undoubtedly increase the load of the server memory and reduce the speed of the system; if you follow the plan 2, you may frequently operate the database, which will also affect the response efficiency; Therefore, it is generally used 方案一+方案二to achieve.
The credibility of paging is to calculate the number of records per page, the total number of pages, and the number of pages. For each page of data, only the start record and the end record need to be calculated.

1.1 rownum

In order oracleto achieve paging, you need to use rownumthis field, rownumnot a real column, it is the number of the row returned from the query, the first row returned is assigned 1, the second row is 2, and so on , This pseudo field can be used to limit the total number of rows returned by the query.
[Because it rownumis always the number of the result set, rownum>1any record that cannot be directly queried because it always starts from 1. 】

Here the following table is an example:

Insert picture description here

Next, use the sqlstatement to query rownum.

select name,age,math,rownum from student;

Let's see what it rownumis like.

Insert picture description here

In fact, each query record has its own rownumnumber, but it is not displayed by default.

Now let's perform the paging operation, each page displays 5 records, query the data on the first page:

select rownum, name,age,math,english from student where rownum<=5;

When this statement is executed, all the records have been queried, but whereonly the first 5 pieces of data are displayed.

Insert picture description here

Pay attention rownumto how much it can only be less than. If the condition is greater than 1, it is impossible to get data, because each piece of data rownumis 1 when it is placed in the result set , and the second is 2. If the first is 1, the condition is not met, then The second piece of data rownumis still 1 when it is placed in the result set , so it continues... For

select rownum, name,age,math,english from student where rownum>2;

The limit is rownumgreater than 2, in fact, no data can be found.

Insert picture description here

So how to get the data of the second page? 【5<rownum<=10】. It can wherebe achieved by using clauses to treat the queried data as the source of the data, so that rownumit becomes an ordinary field, not a pseudo column.

Insert picture description here

The table in the above figure is the data source, and rownumthis column becomes an ordinary column.

select * from (select rownum, name,age,math,english from student) where rownum <=10;

This is the first 10 records of the query. There is a point to note here. The wherelatter rownumis the result set, rownumwhich can only be less than a certain number, and cannot be greater than a certain number:

Insert picture description here

then how to get the rownum[ordinary column in the result table] in the table , Instead of the rownum[pseudo column, can only start from 1] in the result set of this statement .
At this time, you can use an alias to achieve:

select * from (select rownum as rw, name,age,math,english from student) where rw>5 and rw <=10;

Here played an alias rwto achieve fetch 6到10data between here and there is no query result set rownum, rwthe result set of a query inside the brackets rownum, this concept can not be mixed up. As shown in the figure below:

Insert picture description here

aliases can distinguish different sentence result sets rownum. Note here that if you want to sort, rownumthe sequence number will be shuffled, because rownumthe sequence number is determined before the sorting is done first in the result set and then sorting . Let's look at an example:

select rownum,rw2, name,age,math,english from (select rownum as rw2, name,age,math,english from student order by math desc);

Here, the query result set in the brackets is sorted.

Insert picture description here

As shown in the figure above, the result set in the brackets rownumhas been out of order, but the outer layer is rownumstill in order.