Software Environment:
1, Windows NT4.0 Oracle 8.0.4
2, the Oracle installation path is: C: / ORANT
Meaning explanation:
1. Rownum is the number of rows returned from the query, and the first line returned is 1, the second line is 2,
This pseudo-field can be used to limit the number of queries returned.
2, ROWNUM cannot be prefixed in any base table.
Instructions:
There is a product sales table Sale, the table structure is:
Month Char (6) - Month
Sell Number (10, 2) - monthly sales amount
Create Table Sale (Month Char (6), Sell Number;
INSERT INTO SALE VALUES ('200001', 1000);
INSERT INTO SALE VALUES ('200002', 1100);
INSERT INTO SALE VALUES ('200003', 1200);
INSERT INTO SALE VALUES ('200004', 1300);
INSERT INTO SALE VALUES ('200005', 1400);
INSERT INTO SALE VALUES ('200006', 1500);
INSERT INTO SALE VALUES ('200007', 1600);
INSERT INTO SALE VALUES ('200101', 1100);
INSERT INTO SALE VALUES ('200202', 1200);
INSERT INTO SALE VALUES ('200301', 1300);
INSERT INTO SALE VALUES ('200008', 1000);
COMMIT;
SQL> SELECT ROWNUM, MONTH, SELL from Sale where rownum = 1; (can be used in a place to restrict the number of records, guarantees incomparable, such as: implicit cursor)
Rownum Month Sell
-----------------------
1 200001 1000
SQL> SELECT ROWNUM, MONTH, SELL from Sale where rownum = 2; (1 or more can not be recorded)
No record
SQL> SELECT ROWNUM, MONTH, SELL from Sale Where Rownum> 5;
(Because rownum is a pseudo column starting from 1, Oracle believes that this condition is not established, not recorded)
No record
Returns only the first three records
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 Rownu <10
2 minus
3 Select Rownum, Month, Sell From Sale Where Rownum <5; Rownum Month Sell
-----------------------
5 200005 1400
6 200006 1500
7 200007 1600
8 200101 1100
9 200202 1200
I want to sort according to the date, and the correct serial number is marked with ROWNUM (there is 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 1500
7 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 the RowID is also assigned.
SQL> SELECT ROWID, ROWNUM, MONTH, SELL from Sale ORDER by RowId;
Rowid Rownum Month Sell
---------------------------------------
000000E4.0000.0002 1 200001 1000
000000E4.0001.0002 2 200002 1100
00000000E4.0002.0002 3 200003 1200
000000E4.0003.0002 4 200004 1300
000000E4.0004.0002 5 200005 1400
000000E4.0005.0002 6 200006 1500
000000E4.0006.0002 7 200007 1600
000000E4.0007.0002 8 200101 1100
000000E4.0008.0002 9 200202 1200
000000E4.0009.0002 10 200301 1300
000000E4.000A.0002 11 200008 1000
Query 11 records.
Right 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 1500
7 200007 1600
8 200008 1000
9 200101 1100
10 200202 1200
11 200301 1300
Sort by the sales amount and mark the correct serial number with ROWNUM (there is small to large)
SQL> SELECT ROWNUM, MONTH, 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 1200
7 200004 1300
8 200301 1300
9 200005 1400
10 200006 1500
11 200007 1600
Query 11 records.
Using the above method, if you print the report, you want to automatically add a line number in the data that you can, you can use ROWNUM.
Return Article 5-9 Records, sort by month
SQL> Select * from (SELECT ROWNUM ROW_ID, MONTH, SELL
2 from (SELECT MONTH, SELL from Sale Group by Month, Sell))
3 WHERE ROW_ID BETWEEN 5 and 9;
Row_id Month Sell
---------- ----------------
5 200005 1400
6 200006 1500
7 200007 1600
8 200008 1000
9 200101 1100
Oracle: Rownum Does Not Order
Oracle provides the characteristics called ROWNUM, and for each record returned by a query, RowNum returns a number to represent the order. This list is not sorted.
SQL> SELECT Username, Rownum from DBA_USERS ORDER BY Username
Username Rownum
------------------------------
Aurora $ orb $ unauthenticated 6
CTXSYS 10
DBSNMP 4
MDSYS 9
Ops $ Oracle 5
Ordplugins 8
ORDSYS 7
Outln 3
SYS 1
SYSTEM 2
Testuser 11
11 rows selected.
The use of Order By did not solve the problem because ROWNUM has been applied before the record is sorted. Below is an example in which the three records need to return a basic table.
SELECT Username, Rownum from DBA_USERS
WHERE ROWNUM <4 Order by UserName;
Username Rownum
------------------------------ Outln 3
SYS 1
SYSTEM 2
