Parsing oracle rownum

When parsing oracle rownum, I recently used oracle rownum for paging display, and made further analysis and research on rownum. This is summarized as follows, hoping to benefit everyone. For rownum, www.2cto.com is the number of the row returned from the query in the oracle system order. The first row returned is 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: table: student (student) table. The table structure is ID char (6) -- student ID name VARCHAR2 (10) -- name create table student (ID char (6 ), name VARCHAR2 (100); insert into sale values ('000000', 'zhang yi'); insert into sale values ('000000', 'wang 2 '); insert into sale values ('123456', 'Lee 3'); insert into sale values ('123456', 'zhao si'); commit; (1) rownum: If you want to find the information of the first student in the student table that is equal to a certain value, 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, id, name from student where rownum = 1; rownum id name ---------- ------ ------------------------------------------------------- 1. 1. 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 When rownum> 2 is used for the record after the second row, 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 the SQL> select rownum, id, name from student where rownum> 2 cannot be found; 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, when using rownum <3, you can get. 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. 1. 1. 2. 2. 2. 2. 3, it may be necessary to query the data of rownum in a certain range. What should we 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 2 3 200003 Li San (4) rownum and the rownum in Oracle are 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 2 1 200001 Zhang Yi 4 200004 Zhao Si can see, rownum is not the sequence 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 2 3 200001 Zhang Yi 4 200004 Zhao Si this way becomes by name, and mark the correct sequence number with rownum (small to big)