1, first of all, the use of rownumRowNum: A pseudo column added to the result set, that is, the result set followed by a column (emphasis: first to have the result set), simply say RowNum is the match to the conditional result of the serial number. It's always lined up from 1. So your chosen result cannot be 1, and there are other values greater than 1.
1,select int_id, Zh_label, rownum RN from table_name where rownum <= 20; Correct 2,select int_id, Zh_label, rownum RN from table_name where and rownum>10; Mistakes don't get the results.
RowNum is a sequence that adds an ordinal number to the query result. The first record rownum the value of 1, the second is 2, and so on. If you use >,>=,=,between...and these conditions, because the first record obtained from the result set is RowNum 1, it is deleted, then the strip is removed, but its rownum or 1, is deleted, and so on, there is no data;
With the above concept of rownum from different aspects, we can come to understand the use of several rownum
1. Select rownum,c1 from t1 where RowNum!= 10 is the return of the first 9 data? It is a result set that is returned with select ROWNUM,C1 from tablename where RowNum < 10 Is it the same?
Because after you query the result set, after the 9th record is displayed, the subsequent records are!= 10, or >=10, so only the first 9 records are displayed. It can also be understood that RowNum is 9 after the record of the RowNum 10, because the conditions for!=10, so remove, then record up, RowNum is 10, also removed, if down will only show the first 9 records
2. Why RowNum >1 when a record is not found, and rownum >0 or rownum >=1 but always show the record
Since RowNum is added after a query to the result set, it always starts at 1
3. Why between 1 and 10 or between 0 and 10 can find the result, but between 2 and 10 do not get the result
For the same reason, since rownum always starts at 1 rownum cannot be prefixed with the name of any base table;
For example, select int_id, Zh_label, rownum RN from table_name a where a.rownum <= 20; Error Select int_id, Zh_label, rownum RN fro M table_name a where RN <= 20; equally incorrect2, paging query SQL1, when no order by, you can directly filter the first single SELECT *
From (select int_id, Zh_label, rownum RN
From table_name
where RowNum <=) a
where A.rn > 10;
2. When there is an order by, there will be more than one layer of nested SELECT *
From (select A.*, rownum RN
From (select int_id, Zh_label
From table_name
where order by int_id) a
where RowNum <= 2 *) b
where RN > 10;
This is because the select Int_id,zh_label,rownum from table_name where state=0 the order by int_id;
At this point the query results the first data does not start with rownum, the main reason is that Oracle is the first to extract records and then sorted, and Oracle RowNum is in the extraction record has been generated, it is prior to the sort operation, so you must use the subquery first order to get the correct result