Parse Oracle rownum {convert}

Http://www.cnblogs.com/zjrstar/archive/2006/08/31/491090.html Original article

For rownum, it is the number of the row returned from the query that is sequentially allocated by the Oracle system. The first row is allocated 1, and the second row is 2, 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.
Example:
Example Table: Student (student) table with the following structure:
Id char (6) -- Student ID
Name varchar2 (10) -- name
Create Table student (ID char (6), name varchar2 (100 ));
Insert into sale values ('20140901', 'zhang yi ');
Insert into sale values ('20140901', 'wang 2 ');
Insert into sale values ('20170101', 'Lee 3 ');
Insert into sale values ('20140901', 'zhao si ');
Commit;
(1) rownum for query conditions equal to a certain value
If you want to find the information of the first student in the student table, you can use rownum = 1 as the condition. However, if you want to find the second student information in the student table, you cannot find the data using rownum = 2. Because rownum starts from 1, but the natural numbers above 1 are regarded as false when rownum is equal to or equal to the true value. Therefore, rownum = N (The Natural Number of n> 1) cannot be found ).
SQL> select rownum, ID, name from student where rownum = 1; (it can be used to limit the number of returned records to ensure no error, for example, implicit cursor)
SQL> select rownum, ID, name from student where rownum = 1;
Rownum ID name
-------------------------------------------------------------------
1 200001 Zhang Yi
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 the second row, when rownum> 2 is used, the record cannot be found because rownum is a pseudo column always starting from 1, oracle considers that the condition rownum> N (Natural Number of n> 1) is still not true, so records cannot be found.
SQL> select rownum, ID, name from student where rownum> 2;
Rownum ID name
-------------------------------------------------------------------
Then how can we find the record after the second row. You can use the following subquery method to solve the problem. Note that the rownum in the subquery must have an alias; otherwise, the record is not found because rownum is not a column of a table. If the alias cannot be found, you cannot know whether rownum is a subquery column or a primary query column.
SQL> select * from (select rownum No, ID, name from student) where no> 2;
No ID name
-------------------------------------------------------------------
3 200003 Li San
4 200004 Zhao Si
SQL> select * from (select rownum, ID, name from student) Where rownum> 2;
Rownum ID name
-------------------------------------------------------------------
(3) rownum for query conditions smaller than a certain value
If you want to find the previous record of the third record, use rownum <3 to get two records. Obviously, rownum considers the condition of rownum <n (Natural Number of n> 1) as true, so records can be found.
SQL> select rownum, ID, name from student where rownum <3;
Rownum ID name
-------------------------------------------------------------------
1 200001 Zhang Yi
2 200002 Wang 'er
To sum up the preceding situations, you may need to query rownum data in a certain range. What should you do? We can see that the rownum query condition for a value smaller than a certain value is true, rownum is regarded as false for query conditions greater than a certain value, but it can be converted to true indirectly. Subquery is required. For example, to query the data of rownum between the second row and the third row, including the data of the second row and the third row, we can only write the following statement to first let it return the record rows smaller than or equal to three, then, in the primary query, it is judged that the alias column of the new rownum is greater than or equal to two record rows. However, such operations will affect the speed in the big 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 Li San
(4) rownum and sorting
In Oracle, rownum is the sequence number generated when data is retrieved. Therefore, you must pay attention to the specified rowmun rows of data to be sorted.
SQL> select rownum, ID, name from student order by name;
Rownum ID name
-------------------------------------------------------------------
3 200003 Li San
2 200002 Wang 'er
1 200001 Zhang Yi
4 200004 Zhao Si
It can be seen that rownum is not the serial number generated by the name column. The system assigns the number of the record row according to the sequence in which the record is inserted, and the rowid is also allocated sequentially. To solve this problem, you must use the subquery
SQL> select rownum, ID, name from (select * from student order by name );
Rownum ID name
-------------------------------------------------------------------
1 200003 Li San
2 200002 Wang 'er
3 200001 Zhang Yi
4 200004 Zhao Si
In this way, the sequence is sorted by name, and the correct sequence number (from small to large) is marked with rownum)

 

Another article, which seems to be written by DBA, focuses on deeper execution plans and performance issues. The address is http://ryuxy.itpub.net/post/28161/405892.

Finally, in Oracle10g, when we encounter two identical fields, rownum will not appear in parallel, or 12345 in order.

1 colleague 1
2 666
3 555
4 444
5 444
6 3333