Rownum query
See a good article, let's take a look at [Repost] How to correctly use ROWNUM to limit the number returned by the query? Software Environment: 1. Windows NT4.0 Oracle 8.0.4 2, Oracle Installation Path is: C: / ORANT Meaning Explanation: 1. Rownum is the number of rows returned from queries, returned Allocated 1, the second line is 2, so that this pseudo field can be used to limit the total number of queries returned. 2, ROWNUM cannot be prefixed in any base table.
How to use: Existing merchandise sales table Sale, Table structure: Month Char (6) - Month Sell Number (10, 2) - Month Sales CREATE TABLE SALE (Month Char (6), Sell Number; INSERT INTO Sale Values ('200001', 1000); Insert Into Sale Values; Insert Into Sale Values; Insert Into Sale Values ('200004', 1300); Insert Into Sale VALUES ('200005', 1400); Insert Into Sale Values; Insert INTO Sale Values; Insert INTO SALE VALUES ('200101', 1100); Insert Into Sale Values '200202', 1200); Insert Into Sale Values; Insert Into Sale Values ('200008', 1000); Commit; SQL> SELECT ROWNUM, MONTH, SELL from Sale where rownum = 1; Used in a place to restrict the number of returns records, if you do not errors, such as implicit cursors) Rownum Month Sell ----------------------- 1 200001 1000 SQL> SELECT ROWNUM, MONTH, SELL from Sale where rownum = 2; (more than 1 record) No recorded SQL> SELECT ROWNUM, MONTH, SELL from sale where rownum> 5; (because Rownum is always From 1 pseudo column, Oracle believes that this condition is not established, not recorded) No record only returns to the top 3 record SQL> SELECT ROWNUM, MONTH, SELL from Sale Where Rownum <4; Rownum Month Sell --- -------- ------ --------- 1 200001 1000 2 200002 1100 3 200003 1200 How to achieve greater than logic with ROWNUM? (Returns the data between ROWNUMs at 4-10) (Minus operation, speed will be affected) SQL> Select Rownum, Month, Sell from sale where rownum <10 2 minus 3 Select Rownum, Month, Sell From Sale Where Where Wherewnum <5 ; Rownum month sale -------------- --------- 5 200005 1400 6 200006 1500 7 200007 1600 8 200101 1100 9 200202 1200 I want to sort by date, and use ROWNUM marks the correct serial number (there is a small to large) SQL> Select Rownum, Month, Sell From Sale ORDER BY MONTH;
Rownum Month Sell -------------- --------- 1 200001 1000 2 200002 1100 3 200003 1200 4 200004 1300 5 200005 1400 6 200006 15007 200007 1600 11 200008 1000 8 200101 1100 9 200202 1200 10 200301 1300 Query 11 records. It can be found that ROWNUM does not implement our intention, the system is the number of records in the order when the record is recorded, and RowID is also the SQL> SELECT ROWID. , Rownum, Month, Sell from Sale ORDER by Rowid; Rowid Rownum Month Sell -------------------------------------------------------------------------------------------------------------------------- -------- 000000E4.0000.0002 1 200001 1000 00000E4.0001.0002 2 200002 1100 000000 00024.0002 3 200003.0002 4 200004 1300 000000E4.0004.0002 5 200005 1400 000000E4.0005.0002 6 200006 1500 000000E4.0006.0002 7 200007 1600 00000000E4.0007.0002 8 200108.0002 9 200202 1200 000000E4.0009.0002 10 200301 1300 00000000E4.000A.0002 11 200008 1000 query 11 records. Correct usage, use subqueries SQL> Select Rownum, Month, Sell from (SELECT MONTH, Sell From Sale Group by Month, Sell) WHERE ROWNUM <13; Rownum Month Sell -------------- --------- 1 200001 1000 2 200002 1100 3 200003 1200 4 200004 1300 5 200005 1400 6 200006 15007 200007 1600 8 200008 1000 9 200101 1100 10 200202 1200 11 200301 1300 Sort by sales, and use ROWNUM to mark the correct serial number (small to large) SQL> SELECT ROWNUM, MONTH, SELL FROM (SELECT SELL, MONTH FROM Sale Group by Sell, Month) WHERE ROWNUM <13; ROWNUM MONTH SELL -------------- --------- 1 200001 1000 2 200008 1000 3 200002 1100 4 200101 1100 5 200003 1200 6 200202 12007 1300 9 200005 1400 10 200006 1500 11 200007 1600 query 11 records. Using the above methods, if you print the report, you want to automatically add the line number in the isolated data, Rownum can be used.
