One. SQL language profile
SQL overview
SQL is a common data processing language specification for the database. You can complete the following features: extract query data, insert modification delete data, generate modifications and delete database objects, database security control, database integrity, and data protection.
Database objects include tables, views, indexes, synonyms, clusters, triggers, functions, packages, database chains, snapshots, etc. (tablespaces, rollback segments, roles, users). The database manages the data stored therein by the operation of the table.
2. Database query
1) Use the SELECT statement to extract query data from the table. Syntax
SELECT [DISTINCT] {Column1, Column2, ...} from tablename where {conditions}}}}}}}}}}}}}
Note: The SELECT clause is used to specify which columns in the retrieval database, and the FROM clause is used to specify which table or view searches data.
2) The operator and multi-table query WHERE clause is in SELECT. (Like, IS, ...)
The condition in the WHERE clause can be a conditional expression containing the equivalent or iris, or a conditional pattern containing the comparison of IN, NOT IN, BETWEEN, LIKE, IS NOT NULL, etc., can also be single Conditions Expression Compound Conditions by logical operators.
Compare operator => <> = <=! = <>
SQL operator Between ... and ... in Like is Null
NOT BETWEEN ... AND ... NOT IN NOT LIKE IS NOT NULL
Logical Operator and OR Not NOT
3) ORDER BY clause
The ORDER BY clause enables SQL to arrange each return line while displaying the query results, and the order of the return line is determined by the value specified by the ORDER BY clause.
4) Connection inquiry
When performing a database query using the SELECT statement, multiple tables, the data of the view can be combined such that each row of the query result includes data from multiple expressions or views, which is referred to as a connection query.
The method of connecting the query is to specify two or more tables or views that will be connected to the query in the FROM clause of the select command, and tell Oracle how to merge multiple tables in the WHERE clause. According to the conditional expression in the WHERE clause, the connection query can be divided into equality connections and inequality.
5) Subproof
If a select command (Query 1) appears in one clause of another SQL command (Query 2), the query 1 is called the subquery of the query 2.
3. Basic data type (Number, varchar2, date)
ORACEL supports the following internal data types:
l VARCHAR2 grows long strings, up to 2000 characters.
l Number numerical type.
l Long bell character data, up to 2G bytes.
l Date date.
l RAW binary data, up to 255 bytes.
l Long Raw grows binary data, up to 2G bytes.
l ROWID two six strings, indicating the unique address of the row of the table.
l CHAR fixed length character data, up to 255.
4. Common function usage:
A function is similar to an operator, it operates the data item, returns a result. The function is different from the operator in the format, which has a variable element, operable 0, one, two or more variables, forms: function name (zible, change, ...)
The function has the following general type:
l single line function
l Group function
1) The single line function returns a result of each line of the query table or view. It has numeric functions, character functions, date functions, conversion functions, and more.
2) The result returned by the group function is based on the row group rather than a single line, so the packet function is different from the single line function. There can be the following options in many packet functions:
l Distrnct This option makes the group function only consider different values in the variable-element expression.
l ALL This option makes the packet function considers all values and contains all repetitions.
All partial group functions (except count (*) ignore null values. If you have a query of the packet function, there is no return line or only the null value (row of the value of the packet function), then the packet function returns a null value.
l single line function
1) Digital function
ABS takes absolute value POWER Power LN 10 for the base
The N times multiplier log (m, n) m of SQRT square root EXP E is based on the base N
Mathematical operation function: acos Atan Atan2 Cos Cosh Sign Sin Sinh Tan Tanh
CEIL is greater than or equal to the total number
FLOOR is less than or equal to the total number
MOD
Round (n, m) Press M's bit to take a round value if Round (date): will be the date of tomorrow after 12 noon. Round (sysdate, 'y') is the first day of the year
Trunc (n, m) Press M'S bit to take the previous value if trunc (date), and save the time to remove the time
2) Character function
CHR Returns Character Set by Database
Concat (C1, C2) combines two characters C1, C2 into a character, and ||
Replace (C, S, R) Replace the characters in the character c to R, return new characters
SubStr (C, M, N) M is greater than 0, and the character c starts to take N-bit characters from the front M, M is equal to 0 and 1,
M small with 0, character c starts N-bit from the rear m
Translate (C, F1, T1) Character C is converted into a new string according to the rules of F1 to T1.
INITCAP Character The first letters, other characters lowercase
Lower characters all lowercase
Upper characters all uppercase
LTRIM (C1, C2) removes characters in the left side of the character C1
RTRIM (C1, C2)
Trim (C1, C2) removes characters C2 on the left and right sides of the character C1
LPAD (C1, N, C2) character c1 Press the number of bits n to display insufficient bits to replace the left space with C2 string
RPAD (C1, N, C2)
3) Date function
Add_MONTHS (D, N) date value plus N month
Last_day (d) Returns the date of the last day of the month
Months_between (D1, D2) two dates between dies, D1 Next_day (d) Returns the date next day Sysdate Current System Time Dual is a SYS user next empty list, which has only one field Dummy4) conversion function (1) TO_CHAR (date, 'date display format ") TO_CHAR (NUMBER) is used to display or report format alignment TO_DATE (Char, 'Date Display Format ") TO_LOB converts the long field to the LOB field TO_NUMBER (CHAR) is used to calculate or relatively small 5) Conversion function (2) TO_DATE Date Display Format YYYY YEAR YYY YY Y YY Y Q quarter MM Month Mon W Week WW, IW (Week of Year) (Description: Week is a week from the ISO standard, from January 1st to the next seven days for a week, not necessarily from Monday to Sunday) DD day DY DY HH24 hours hh12 hh Mi minutes SS seconds If you want a fixed date display format, you can write a row of parameters in the parameter file in the database. NLS_DATE_FORMAT = YYYY-MM-DD HH24: MI: SS can set NLS_DATE_FORMAT = YYYY in the Unix environment variable or NT registry MM-DD HH24: MI: SS 6) Conversion function (3) If you want a fixed date display format, you can use the alter session command to change SQL> ALTER Session Set NLS_DATE_FORMAT = 'YYYY-MM-DD HH24: MI: SS' Its action is as follows: Initialization Parameter Environment Variable, ENVIRONMENT VARIABLE Alter Session Command 7) Conversion function (4) TO_CHAR (NUMBER) Digital Display Format 9 digits 0 Number front to make 0 TO_CHAR (-1200, '00000.00') Location , Tagged comma with the left side of the digital display format L According to the database character set currency symbol to_CHAR (-1200, 'L9999.99') B put the number 0 as space, use the right side of the digital display format Negative marking to_CHAR (-1200, '9999.99mi ") on the right side of Mi PR enclosed negative to_char (-1200, '9999.99pr') EEEE uses an index mode to indicate to_char (-1200, '9999.99eeee') 8) Enter the character, return the number of numbers Instr (C1, C2) character C2 appears in the position of C1, does not appear, return 0, often used for blurring query Length (c) calculates the length of the database, calculates the length of the character C, is related to the character set of the database, one Chinese character is 1 9) Function NVL (EXPR1, EXPR2) function with logic comparison Explanation: if expr1 = null return expr2 Else Return EXPR1 10) Decode (AA, V1, R1, V2, R2 ....) function Explanation: if aa = v1 Ten R1IF AA = V2 THEN RETURN R2 .. ... Else Return Null Example: Decode (ID, 1, 'DEPT SALE', 2, 'DEPT TECH') l The collection function is often used with Group By. 1) Collection function list AVG (DistINCT | All | N) Take the average Count (DistINCT | All | N | EXPR | *) Statistics Max (DistINCT | All | N) takes the maximum value Min (DistINCT | All | N) Take the minimum value SUM (Distinct | All | N) Getage STDDEV (DistINCT | All | N) takes a bias value, if the content selected in the group is the same, the result is 0 Variance (Distinct | All | N) Take a squarefia value 2) Syntax using a collection function Select Column, Group_Function from Table WHERE CONDITION GROUP BY Group_BY_EXPRESSION Having group_condition order by column; 3) Precautions when using count Select count (*) from table; SELECT Count from Table; They are all recorded in the statistics table, if there is no PK, it is better SELECT Count (all field name) from table; SELECT Count from Table; The number of fields that do not count NULL Sum, AVG is ignored by null fields 4) Restriction conditions when using Group B SELECT field name cannot be casual, to be included in the field of Group By GROUP BY ORDER BY can not use position symbols and alias Limit the display result of Group By, with a Having condition 5) example SQL> Select Title, Sum (Salary) Payroll from S_EMP Where title Like 'VP%' Group by Title Having Sum (SALARY)> 5000 Order by Sum (SALARY) DESC; Find the number of records repeated in a table, and display SQL> SELECT (DUPLICATE FIELD NAMES) from Table_name Group by (list outfields) having count (*)> 1; 5. Data manipulation language command: The Database Manipulation Language (DML) command is used to query and manipulate data in the mode object, which does not imply current transactions. It contains commands such as Update, INSERT, DELETE, Explain Plan, SELECT, and LOCK TABLE. Let's take a brief introduction: 1) Update Tablename Set {Column1 = Expression1, Column2 = Expression2, ...} where {conditions} E.g: S QL > Update EMP Set job = 'manager'where ename =' maptin '; SQL> SELECT * FROM EMP; The UPDATE clause indicates that the database to be modified is EMP, and use the WHERE clause to limit the data on the number of employees with the name (ename) to 'martin', the set clause describes the modified way, ie the 'martion' The work name (JOB) is changed to 'marager'. 2) INSERT INTO TABLENAME {Column1, Column2, ...} Values {Expression1, Expression2, ...}; For example: SQL> SELECT INTO DEPT (DNAME, DEPTNO) VALUES ('Accounting', 10) 3) delete from tablename where {conditions}; For example: SQL> Delete from EMP WHERE EMPNO = 7654; The delete command deletes a record, and the delete command can only delete the entire line without deleting some of the data in a row. 4) Transaction control command Submit command: You can make the database to make the database permanently. Set autocommit as allowed: SQL> Set AutoCommit ON; Rollback command (ROLLBACK): Eliminate all modifications after the last commit command, so that the content of the database is restored to the status after the last commit execution. The method is: SQL> ROLLBACK; Second. Oracle Extension PL / SQL Introduction 1. PL / SQL overview. PL / SQL is an extension of Oracle's SQL specification, which is a block structure language, which constitutes a basic unit (process, function, and no name) of a PL / SQL program, which can contain any number of nesting. This program structure supports stepwise method to solve problems. A block (or sub-block) combines logically related instructions and statements, in its form: Declare --- Description Begin --- Statement sequence EXCEPTION --- Exception handler END; It has the following advantages: l Support SQL; l High productivity; l Performance; l can be known; l Integrate with Oracle. 2. PL / SQL architecture The PL / SQL running system is a kind of technology, not a separate product, which can believe that this technology is a machine of the PL / SQL block and subroutine, which can receive any valid PL / SQL block or subroutine. as the picture shows: PL / SQL block PL / SQL machine PL / SQL block Process Actuator SQL statement The PL / SQL machine can perform a procedural statement, and send the SQL statement to the SQL statement actuator on the Oracle server. An unnamed PL / SQL block can be embedded in an Oracle Precipiler or OCI program. If Oracle has an Procedural Option, a famous PL / SQL block (subroutine) can be compiled separately and is permanently stored in the database, ready to execute. 3. PL / SQL foundation: PL / SQL has a character set, reserved word, punctuation, data type, strict grammar, etc., which is the same as SQL, and now introduce. 1) Data type: as shown in the table below type of data Subtype Purity type Numerical value Binary_integer Natural, Positive Number Dec, Decimal, Double Precision, Ploat, INTEGER, INT, NUMERIC, REAL, Smallint character Charr Character, String VARCHAR2 VARCHAR Long Long Raw Raw Rawid logic Boolean date Date combination Types of recording Record table TABLE 2) Variables and constants In the PL / SQL program, the values can be stored in variables and constants. When the program is executed, the value of the variable can be changed, and the value of the constant cannot be changed. 3) Program block structure: Declare Variable description section; Begin Execute a part of statement; [EXception Exception processing section;] END; 4. Control statement: Branch statement: IF condition the SEQUENCE_OF_STATEMENTS; END IF; IF condition the SEQUENCE_OF_STATEMENT1; Else SEQUENCE_OF_STATEMENT2; END IF; IF condition1 Then SEQUENCE_OF_STATEMENT1; Elsif condition2 Then SEQUENCE_OF_STATEMENT2; Elsif condition3 Then SEQUENCE_OF_STATEMENT3; END IF; 5. Circular statement: Loop SEQUENCE_OF_STATEMENTS; IF condition the EXIT; END IF; End Loop ; While Condition Loop SEQUENCE_OF_STATEMENTS; End Loop ; For counter in Lower_bound..higher_bound Loop SEQUENCE_OF_STATEMENTS; End Loop ; 6. Subprint: Store procedure: CREATE Procedure Process Name (Parameter Description 1, Parameter Description 2, ...) IS [Partial description] Begin Execute a statement; END process name; Storage function: CREATE FUNCTION function name (parameter description 1, parameter description 2, ...) Return Type IS [Partial description] Begin Execute a statement; End function name; Three.decode () function usage skills 1. Meaning explanation: Decode (condition, value 1, translation value 1, value 2, translation value 2, ... value n, translation value n, default value) The meaning of this function is as follows: IF condition = value 1 Then Return (Translation value 1) ELSIF Condition = Value 2 Then Return (Translation Value 2) ... Elsif Condition = Value N Then Return (Translation Value N) ELSE RETURN (Default) End IF 2. How to use: 1) Compare a small SELECT DECODE (SIGN (Variable 1-Variable 2), - 1, Variable 1, Variable 2) from Dual; - Take a smaller value SIGN () function According to a certain value is 0, the positive or negative numbers, Returns 0, 1, -1, for example: variable 1 = 10, the variable 2 = 20, SIGN (Variable 1-Variable 2) returns -1, Decode decoding result is "variable 1", and reaches the purpose of taking a smaller value. Table, view structure transformation existing a commodity sales table Sale, table structure: month char (6) - month Sell Number (10, 2) - Existing data from monthly sales amount: 2000000008 1200002 11002008008 1400002006 150000007 1600200101 1100200202 1200200301 1300 Data want to convert to the following structure: Year char (4) - Year Month1 Number (10, 2) - January Sales amount MONTH2 Number (10, 2) - February sales amount Month3 Number (10 2) - March sales amount MONTH4 NUMBER (10, 2) - Sales amount MONTH5 NUMBER (10, 2) - May 6 Number (10, 2) - June sales amount Month7 Number (10, 2) - July sales amount MONTH8 NUMBER (10, 2) - August Sales amount MONTH9 Number (10, 2) - September Sales amount MONTH10 Number (10, 2) - Octa sales amount Month11 Number (10, 2) - November Sales Cost MONTH12 Number (10, 2) - December Sales amount Structure Transformation SQL statement: Create or Replace Viewv_sale (Year, Month1, Month2, Month3, Month4, Month5, MONTH6, MONTH7, MONTH8, MONTH9, MONTH10, MONTH11, MONTH12) AS SELECT SUBSTRB (Month, 1, 4), SUM (Decode (Substrb (Month, 5, 2), '01', Sell, 0), SUM Decode (Substrb (Month, 5, 2), '02', Sell, 0)), SUM (Decode (Substrb (Month, 5, 2), '03', Sell, 0), SUM (Decode (Substrb Month, 5, 2), '04', Sell, 0), SUM (Decode Substrb (Month, 5, 2), '05', Sell, 0)), SUM (Decode (Substrb (Month, 5, 2), '06', Sell, 0), SUM (Decode (Substrb (Month, 5, 2), '07', Sell, 0), SUM (Decode (Substrb (Month, 5, 2), '08', Sell, 0)), SUM (Decode (Substrb (Month, 5, 2) , '09, Sell, 0), SUM (Decode (Substrb (Month, 5, 2),' 10 ', Sell, 0)), SUM (Decode (Substrb (Substrb (Month, 5, 2),' 11 ' , Sell, 0)), SUM (Decode (Substrb (Month, 5, 2), '12', Sell, 0)) from sales by Substrb (Month, 1, 4); four.NULL use details 1. Meaning explanation: Q: What is NULL? A: When we don't know what data, it is not known, you can use null, we call it empty, oracle, the length of the null value is zero. Oracle allows the fields of any data type to be empty, except for the following: 1, the primary key, 2, definition has added the field description: 1, equivalent to no value It is unknown. 2, NULL and 0, empty strings, spaces are different. 3. Do add, reduce, multiply, divide the empty value, and the result is still empty. 4, NULL's processing uses NVL functions. 5. Use the keywords with "is null" and "is not null" when comparing. 6, null value cannot be indexed, so some in line with the conditions may not be found, in count (*), use NVL (column name, 0) to check. 7. Sort is larger than other data (index default is descending order, small → big), so NULL value is always in the end. 2. How to use: SQL> SELECT 1 from dual where null = NULL; No record SQL> SELECT 1 from dual where null = ''; No record SQL> SELECT 1 from dual where '' = ''; No record SQL> SELECT 1 from dual where null is null; 1 --------- 1 SQL> SELECT 1 from Dual WHERE NVL (NULL, 0) = NVL (NULL, 0); 1 --------- 1 Declaration, minus, multiply, divided operations for null values, and the result is still empty. SQL> SELECT 1 NULL from DUAL; SQL> SELECT 1-NULL from DUAL SQL> SELECT 1 * NULL from DUAL; SQL> SELECT 1 / NULL from DUAL Query a record. Note: This record is the NULL in the SQL statement. Set some listed as null value Update table1 set column 1 = NULL WHERE list 1 is not null; 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); INSERT INTO SALE (MONTH) VALUES ('200009'); (Note: This recorded SELL value is empty) COMMIT; 12 records SQL> SELECT * from Sale Where Sell Like '%'; Month Sell ------ --------- 200001 1000 200002 1100 200003 1200 200004 1300 200005 1400 200006 1500 200007 1600 200101 1100 200202 1200 200301 1300 200008 1000 Query 11 records. Result Description: Query results show that this SQL statement query does not list the value of NULL At this time, the field is required to be NULL. SQL> SELECT * from Sale Where Sell Like '%' Or Sell is NULL; SQL> SELECT * from Sale where nVL (Sell, 0) LIKE '%' Month Sell ------ --------- 200001 1000 200002 1100 200003 1200 200004 1300 200005 1400 200006 1500 200007 1600 200101 1100 200202 1200 200301 1300 200008 1000 200009 Query 12 records. Oracle's null value is such a usage, we are best to be familiar with its conventions to prevent the results of the results are incorrect. V. How to find, delete repetitive records in the table 1. Problem proposes: When we want to create a unique index for a table, if the table has a repetitive record, it cannot be created. 2. Method principle: 1) ORACLE, each record has a ROWID, and RowID is unique in the entire database, and the RowID determines which data file, block, and line on each record is in Oracle. 2) In repeated records, all columns may be the same, but the RowId will not be the same, so as long as it is determined that the maximum ROWID has the maximum ROWID, the rest is removed. 3) The following statement is used to use 3 tricks: RowID, subquery, alias. 3. Implementation method: SQL> CREATE TABLE A 2 BM Char (4), - Code 3 mc varchar2 (20) - Name 4) 5 / The table has been established. SQL> INSERT INTO A VALUES ('1111', '1111'); SQL> INSERT INTO A VALUES ('1112', '1111'); SQL> INSERT INTO A VALUES ('1113', '1111'); SQL> INSERT INTO A VALUES ('1114', '1111'); SQL> INSERT INTO A SELECT * FROM A; Insert 4 records. SQL> commit; Completely submit. SQL> SELECT ROWID, BM, MC from A; Rowid BM MC --------------------------- 000000D5.0000.0002 1111 1111 000000D5.0001.0002 1112 1111 000000D5.0002.0002 1113 1111 000000D5.0003.0002 1114 1111 000000D5.0004.0002 1111 1111 000000D5.0005.0002 1112 1111 000000D5.0006.0002 1113 1111 000000D5.0007.0002 1114 1111 Query 8 records. Isolated repeated record SQL> SELECT ROWID, BM, MC from a where a.rowid! = (SELECT MAX (ROWID) from a b where a.bm = b.bm and a.mc = b.mc); Rowid BM MC ---------------------------------------- 000000D5.0000.0002 1111 1111 000000D5.0001.0002 1112 1111 000000D5.0002.0002 1113 1111 000000D5.0003.0002 1114 1111 Delete repeat SQL> Delete from a a a where a.rowid! = (Select max (rowid) from a b where a.bm = b.bm and a.mc = b.mc); Delete 4 records. SQL> SELECT ROWID, BM, MC from A; Rowid BM MC ---------------------------------------- 000000D5.0004.0002 1111 1111 000000D5.0005.0002 1112 1111 000000D5.0006.0002 1113 1111 000000D5.0007.0002 1114 1111 6. How to correctly use ROWNUM to limit the number of rows returned by the query 1. 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, and this pseudo field can be used to limit the total number of lines returned by the query. 2) ROWNUM cannot be prefixed in any base table. 2. How to use: 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 Rownu <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, SELL2 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
