Parsing Oracle's RowNum

Recently, I have made further analysis and research on rownum when I use Oracle's rownum to realize pagination display. Now summarized as follows, hope to bring you a harvest.
For rownum It is the number of the Oracle system order assigned to the rows 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, and rownum cannot be prefixed with any table name.
To illustrate:
For example, table: Student (Student) table, the table structure is:
ID Char (6)--School number
Name VARCHAR2 (10)--Name
CREATE TABLE Student (ID char (6), name VARCHAR2 (100));
INSERT into sale values (' 200001 ', ' Zhang Yi ');
INSERT into sale values (' 200002 ', ' King Two ');
INSERT into sale values (' 200003 ', ' lie Triple ');
INSERT into sale values (' 200004 ', ' Zhao Si ');
Commit
(1) rownum for query conditions equal to a value
If you want to find information about the first student in the student table, you can use Rownum=1 as a condition. But to find the information of the second student in the Student's table, the data was not found using rownum=2 results. Because rownum are all starting from 1, but more than 1 of the natural number in rownum do equal to the judgment is considered false condition, so can not find rownum = N (n>1 natural number).
Sql> Select Rownum,id,name from student where rownum=1; (can be used to limit the number of records returned, guaranteed to be error-prone, such as: implicit cursors)
Sql> Select Rownum,id,name from student where rownum=1;
ROWNUM ID NAME
---------- ------ ---------------------------------------------------
1 200001 Zhang One
Sql> Select Rownum,id,name from student where rownum = 2;
ROWNUM ID NAME
---------- ------ ---------------------------------------------------
(2) RowNum for query conditions that are greater than a value
If you want to find records from the second row records, when using Rownum>2 is not a record, because rownum is always a pseudo-column starting from 1, Oracle thinks rownum> N (natural number of n>1) This condition is still not established, So I can't find the records.
Sql> Select Rownum,id,name from student where RowNum >2;
ROWNUM ID NAME
---------- ------ ---------------------------------------------------
How to find the second line after the record AH. You can use the following sub-query methods to resolve. Note that the rownum in a subquery must have an alias, or the record will not be detected, because rownum is not a column of a table, and if you cannot alias it, you cannot know whether rownum is a column of a subquery or a column of a primary query.
Sql>select * FROM (select RowNum No, id,name from student) where no>2;
NO ID NAME
---------- ------ ---------------------------------------------------
3 200003 Lie triple
4 200004 Zhao Si
Sql> SELECT * FROM (select Rownum,id,name from student) where rownum>2;
ROWNUM ID NAME
---------- ------ ---------------------------------------------------
(3) RowNum for query conditions that are less than a certain value
If you want to find a third record of previous records, you can get two records when using Rownum<3. Obviously rownum for rownum<n (the natural number of n>1) is considered to be set up, so records can be found.
Sql> Select Rownum,id,name from student where RowNum <3;
ROWNUM ID NAME
---------- ------ ---------------------------------------------------
1 200001 Zhang One
2 200002 Wang ER
In several cases, may sometimes need to query rownum in a certain interval of data, how to do it can be seen from the rownum to a value less than a query condition is true, rownum for a value greater than a query condition is directly considered false, But it can be indirectly converted to true. Then you must use the subquery. For example, to query the data between the second and third rows of rownum, including the second row and the third row of data, we can only write the following statement, first let it return a record line less than or equal to three, and then in the main query to determine the new rownum column is greater than or equal to two of the record row. However, such operations can affect speed in a large data set.
Sql> SELECT * FROM (select RowNum no,id,name from student where rownum<=3) where no >=2;
NO ID NAME
---------- ------ ---------------------------------------------------
2 200002 Wang ER
3 200003 Lie triple
(4) RowNum and sorting
The rownum in Oracle is the sequence number that is generated when fetching data, so it is important to be aware of the rowmun rows of data that you want to specify for the sorted data.
Sql> Select RowNum, id,name from student order by name;
ROWNUM ID NAME
---------- ------ ---------------------------------------------------
3 200003 Lie triple
2 200002 Wang ER
1 200001 Zhang One
4 200004 Zhao Si
As you can see, rownum is not the ordinal number generated by the Name column. The system is the number of records in the order in which records are inserted, and ROWID is assigned sequentially. To solve this problem, you must use a subquery
Sql> Select RowNum, Id,name from (SELECT * from Student order by name);
ROWNUM ID NAME
---------- ------ ---------------------------------------------------
1 200003 Lie triple
2 200002 Wang ER
3 200001 Zhang One
4 200004 Zhao Si
This will be sorted by name, and the correct sequence number (small to large) is marked with rownum.