oracle's rownum in depth analysis

  • 2020-11-20 06:17:51
  • OfStack

Recently, When I used oracle's rownum to realize paging display, I made a step further analysis and research on rownum. Now conclude as follows, hope can bring harvest to everybody.

For rownum it is the number of rows that the oracle system sequentially assigns to the row returned from the query, row 1 is assigned 1, row 2 is assigned 2, and so on. This pseudo-field can be used to limit the total number of rows returned by the query, and rownum cannot be prefixed with the name of any table.
For example:
For example, the table student(student) has the following structure:

 
ID    char(6)      -- Student id  
name    VARCHAR2(10)   -- The name  
create table student (ID char(6), name VARCHAR2(100)); 
insert into sale values('200001', "Zhang 1'); 
insert into sale values('200002', 'the king 2'); 
insert into sale values('200003', 'li 3'); 
insert into sale values('200004', "Zhao 4'); 
commit; 

(1) rownum for query conditions equal to a certain value
If you want to find information about students in item 1 in the student table, you can use rownum=1 as a condition. However, in order to find the information of students in item 2 in the student table, rownum=2 result could not be used to find the data. Since rownum all start from 1, but the natural Numbers above 1 are considered to be false conditions when rownum makes the equal judgment, it cannot be found that rownum = n (n) > The natural number of 1.
SQL > select rownum,id,name from student where rownum=1; (Can be used to limit the number of returned records, to ensure no errors, such as: implicit cursor)
SQL > select rownum,id,name from student where rownum=1;
ROWNUM ID NAME
---------- ------ ---------------------------------------------------
1 200001 1
SQL > select rownum,id,name from student where rownum =2;
ROWNUM ID NAME
---------- ------ ---------------------------------------------------
(2) rownum for query conditions greater than a certain value
If you want to find the record from line 2, when using rownum > 2 is untraceable because rownum is a pseudo column that always starts at 1 and Oracle considers rownum > n(n > This condition still does not hold, so the record cannot be found
SQL > select rownum,id,name from student where rownum > 2;
ROWNUM ID NAME
---------- ------ ---------------------------------------------------
How do you find the record after line 2? The following subquery method can be used to solve this problem. Note that rownum in the subquery must be aliased or the record will not be retrieved because rownum is not a column of a table, and you cannot know if rownum is a column of the subquery or the main query if you do not have the alias.
SQL > select * from(select rownum no ,id,name from student) where no > 2;
NO ID NAME
---------- ------ ---------------------------------------------------
3 200003 li
Zhao 4 4 200004
SQL > select * from(select rownum,id,name from student)where rownum > 2;
ROWNUM ID NAME
---------- ------ ---------------------------------------------------
(3) rownum for query conditions less than a certain value
If you want to find the previous record for the 3rd record, when using rownum < Three is going to get two records. Obviously rownum for rownum < n ((n > The condition of the natural number of 1 is considered to be true, so the record can be found.
SQL > select rownum,id,name from student where rownum < 3;
ROWNUM ID NAME
---------- ------ ---------------------------------------------------
1 200001 1
200002 king 2
To sum up, it may sometimes be necessary to query the data of rownum in a certain interval, how to do? It can be seen from the above that rownum is artificial for the query condition less than a value of true, rownum for the query condition greater than a value of false directly considered as false, but can indirectly let it be considered as true. Then you must use subqueries. For example, to query rownum's data between rows 2 and 3, including rows 2 and 3, we can only write the following statement, which first returns rows less than or equal to 3, and then determines in the main query the rows with the new rownum's unique names greater than or equal to 2. But such operations can affect speed in big data sets.
SQL > select * from (select rownum no,id,name from student where rownum < =3 ) where no > =2;
NO ID NAME
---------- ------ ---------------------------------------------------
200002 king 2
3 200003 li
(4) rownum and sorting
rownum in Oracle is the sequence number generated when fetching the data, so you must pay attention to the rowmun row data if you want to specify the sorted data.
SQL > select rownum ,id,name from student order by name;
ROWNUM ID NAME
---------- ------ ---------------------------------------------------
3 200003 li
200002 king 2
1 200001 1
Zhao 4 4 200004
As you can see, rownum is not an ordinal number generated according to the name column. The system assigns the row Numbers in the order in which the records were inserted, and rowid is also assigned sequentially. To solve this problem, you must use subqueries
SQL > select rownum ,id,name from (select * from student order by name);
ROWNUM ID NAME
---------- ------ ---------------------------------------------------
1 200003 li
200002 king 2
3 pieces of 1 200001
Zhao 4 4 200004
This is sorted by name, with the correct number (small to large) in rownum


Related articles: