Select Format:
SELECT [All | DISTINCT] < field expression 1[,< field expression 2[,...]
From < table name 1>,< table name 2>[, ...]
[WHERE < screen selection condition expression]
[GROUP BY < group expression > [having< Group conditional expression]]
[Order by < Field >[ASC | DESC]]
Statement Description:
[] square brackets are optional
[GROUP BY < group expression > [having< Group conditional expression]]
The result is grouped by the value of the < group expression >, which is equal to a set of records, with the "having"
A phrase is output only if the group satisfies the specified criteria.
[Order by < Field >[ASC | DESC]]
Display results to sort by < field > value in ascending or descending order
Practice:
1: Table Hkb_test_sore take out the records of the top 5 of the score sore,
2: Take the 5th place record
1, answer select a.sore_id, A.sore
From (SELECT * to Hkb_test_sore ORDER BY sore Desc) a
where RowNum <=5
2, answer select a.sore_id, A.sore
from (SELECT * to Hkb_test_sore ORDER BY sore desc) a
where rownum <=5
minus
Select a.sore_id, A.sore
from (SELECT * to Hkb_test_sore ORDER BY sore desc) a
where rownum <=4;
3: Query two fractions of records
SELECT *
from Hkb_test_sore a
where a.sore = (select sore
&nbs p; from Hkb_test_sore A
GROUP BY A.sore
Having count (a.sore) = 2);
The difference between union,union All,intersect,minus:
Sql> select * from Hkb_test2;
X Y
---- -----
A 1
B 2
C 3
G 4
Sql> select * from Hkb_test3;
X Y
---- -----
A 1
B 2
E 3
F 4
Sql> select * from Hkb_test2;
X Y
---- -----
A 1
B 2
C 3
G 4
Sql> select * from Hkb_test3;
X Y
---- -----
A 1
B 2
E 3
F 4
Sql> SELECT * from Hkb_test2
2 Union
3 SELECT * from Hkb_test3;
X Y
---- -----
A 1
B 2
C 3
E 3
F 4
G 4
6 Rows selected
Sql> SELECT * from Hkb_test2
2 UNION ALL
3 SELECT * from Hkb_test3;
X Y
---- -----
A 1
B 2
C 3
G 4
A 1
B 2
E 3
F 4
8 Rows selected
Sql> SELECT * from Hkb_test2
2 intersect
3 SELECT * from Hkb_test3;
X Y
---- -----
A 1
B 2
Sql> SELECT * from Hkb_test2
2 minus
3 SELECT * from Hkb_test3;
X Y
---- -----
C 3
G 4
Look at a complete example from the above example
Sql>
Sql>
Sql>--Create demo table
Sql> CREATE TABLE Employee (
2 ID VARCHAR2 (4 BYTE) not NULL primary key,
3 first_name VARCHAR2 (BYTE),
4 last_name VARCHAR2 (BYTE),
5 start_date Date,
6 end_date Date,
7 Salary Number (8,2),
8 City VARCHAR2 (BYTE),
9 Description VARCHAR2 (MB)
10)
11/
Table created.
Sql>
Sql>--Prepare data
sql> INSERT INTO Employee (ID, first_name, last_name, start_date, end_date, S Alary, city, Description)
2 values (' ', ' Jason ', ' Martin ', to_date (' 19960725 ', ' YYYYMMDD '), to_date (' 20060725 ', ' YYYYMMDD '), 1234.5 6, ' Toronto ', ' Programmer ')
3/
1 row created.
sql> INSERT INTO Employee (ID, first_name, last_name, start_date, end_date, S Alary, city, Description)
2 values (' ', ' Alison ', ' Mathews ', To_date (' 19760321 ', ' YYYYMMDD '), to_date (' 19860221 ', ' YYYYMMDD '), 6661.7 8, ' Vancouver ', ' Tester ')
3/
1 row created.
sql> INSERT INTO Employee (ID, first_name, last_name, start_date, end_date, S Alary, city, Description)
2 values (', ' James ', ' Smith ', to_date (' 19781212 ', ' YYYYMMDD '), to_date (' 19900315 ', ' YYYYMMDD '), 6544.7 8, ' Vancouver ', ' Tester ')
3/
1 row created.
sql> INSERT INTO Employee (ID, first_name, last_name, start_date, end_date, S Alary, city, Description)
2 values (' ', ' Celia ', ' Rice ', to_date (' 19821024 ', ' YYYYMMDD '), to_date (' 19990421 ', ' YYYYMMDD '), 2344.7 8, ' Vancouver ', ' Manager '
3/
1 row created.
sql> INSERT INTO Employee (ID, first_name, last_name, start_date, end_date, S Alary, city, Description)
2 values (' ', ' Robert ', ' Black ', to_date (' 19840115 ', ' YYYYMMDD '), to_date (' 19980808 ', ' YYYYMMDD '), 2334.7 8, ' Vancouver ', ' Tester ')
3/
1 row created.
sql> INSERT INTO Employee (ID, first_name, last_name, start_date, end_date, S Alary, city, Description)
2 values (' ', ' Linda ', ' Green ', to_date (' 19870730 ', ' YYYYMMDD '), to_date (' 19960104 ', ' YYYYMMDD '), 4322.7 8, ' New York ', ' Tester '
3/
1 row created.
sql> INSERT INTO Employee (ID, first_name, last_name, start_date, end_date, S Alary, city, Description)
2 values (' Modified ', ' David ', ' Larry ', to_date (' 19901231 ', ' YYYYMMDD '), to_date (' 19980212 ', ' YYYYMMDD '), 7897.7 8, ' New York ', ' Manager '
3/
1 row created.
sql> INSERT INTO Employee (ID, first_name, last_name, start_date, end_date, S Alary, city, Description)
2 values (', ' James ', ' Cat ', to_date (' 19960917 ', ' YYYYMMDD '), to_date (' 20020415 ', ' YYYYMMDD '), 1232.7 8, ' Vancouver ', ' Tester ')
3/
1 row created.
Sql>
Sql>
Sql>
Sql>--Display data in the table
Sql> SELECT * from Employee
2/
ID first_name last_name start_dat end_date SALARY City DESCRIPTION
---- -------------------- -------------------- --------- --------- ---------- ---------- ---------------
Jason Martin 25-jul-96 25-jul-06 1234.56 Toronto Programmer
Alison Mathews 21-mar-76 21-feb-86 6661.78 Vancouver Tester
James Smith 12-dec-78 15-mar-90 6544.78 Vancouver Tester
Celia Rice 24-oct-82 21-apr-99 2344.78 Vancouver Manager
Robert Black 15-jan-84 08-aug-98 2334.78 Vancouver Tester
Linda Green 30-jul-87 04-jan-96 4322.78 New York Tester
David Larry 31-dec-90 12-feb-98 7897.78 New York Manager
James Cat 17-sep-96 15-apr-02 1232.78 Vancouver Tester
8 rows selected.
Sql>
Sql>
sql> SELECT ID, first_name, last_name from employee
2/
ID first_name last_name
---- -------------------- --------------------
Jason Martin
Alison Mathews
James Smith
Celia Rice
Robert Black
Linda Green
Modified David Larry
James Cat
8 rows selected.
How to implement select Top N in Oracle
1. Implement select Top N in Oracle
Because Oracle does not support select top statements, it is often used in Oracle to implement a SELECT top N query with a combination of order by and RowNum.
Simply put, the implementation method looks like this:
SELECT Column Name 1 ... Column name n from
(SELECT column Name 1 ... Column name n from table name order BY column name 1 ... Column name N)
WHERE rownum <= N (number of extracted records)
ORDER BY RowNum ASC
Here's a quick example to illustrate.
Customer table Customer (Id,name) has the following data:
ID NAME
-A
Second
Third
Forth
Fifth
Modified Sixth
Modified Seventh
Eighth
Ninth
Ten Tenth
One last
The SQL statements for the first three customers by name letter are as follows:
SELECT * FROM
(SELECT * from CUSTOMER ORDER by NAME)
WHERE rownum <= 3
ORDER BY RowNum ASC
The output results are:
ID NAME
Eighth
Fifth
-A
2. Extract m (m <= N) records in top N records
After we get the top n data, we can consider starting from rownum in order to extract the record of section m in this N record. As we know, RowNum is a hidden fragment of the data number in the record table, so you can extract the record's rownum at the same time as the top n record, and then extract the record number m from the N records, even if we want to get the result.
From the above analysis you can easily get the following SQL statement.
SELECT Column Name 1 ... Column name n from
(
SELECT rownum recno, column name 1 ... Column name Nfrom
(SELECT column Name 1 ... Column name n from table name order BY column name 1 ... Column name N)
WHERE rownum <= N (number of extracted records)
ORDER BY RowNum ASC
)
WHERE recno = m (M <= N)
Based on the data of the same table above, the SQL statement that gets the information of the second customer sorted by the letter of name should read as follows:
SELECT ID, NAME from
(
SELECT rownum recno, ID, NAME from
(SELECT * from CUSTOMER ORDER by NAME)
WHERE rownum <= 3
ORDER by RowNum ASC)
WHERE RECNO = 2
The result is:
ID NAME
Fifth
3. Extract the nth record in a recordset sorted in some way
In the 2 note, when m = N, that is the result of our title. In fact, 2 of the n>m part of the data is basically not used, we are simply to illustrate the convenience of adoption.
As described above, the SQL statement should be:
SELECT Column Name 1 ... Column name n from
(
SELECT rownum recno, column name 1 ... Column name Nfrom
(SELECT column Name 1 ... Column name n from table name order BY column name 1 ... Column name N)
WHERE rownum <= N (number of extracted records)
ORDER BY RowNum ASC
)
WHERE recno = N
Then, the SQL statement for the example in 2 is:
SELECT ID, NAME from
(
SELECT rownum recno, ID, NAME from
(SELECT * from CUSTOMER ORDER by NAME)
WHERE rownum <= 2
ORDER BY RowNum ASC
)
WHERE RECNO = 2
The results are:
ID NAME
Fifth
4. Extract the X record from the beginning of section m of the recordset, sorted in some way
3 is only a case of taking a record, when we need to extract more than one record, at this time the value of N in 2 should be in the range of n >= (M + X-1), when the most economical value is to wait for the good time. Of course the final extraction condition is not RECNO = n, it should be recno BETWEEN m and (M + X-1), so the following SQL statement is:
SELECT Column Name 1 ... Column name n from
(
SELECT rownum recno, column name 1 ... Column name Nfrom
(
SELECT Column Name 1 ... Column name n from table name order BY column name 1 ... Column name N)
WHERE rownum <= N (n >= (M + X-1))
ORDER BY RowNum ASC
)
WHERE recno BETWEEN m and (M + X-1)
Also, take the above data as an example, the SQL statement that extracts the 3 records at the beginning of the alphabetical 2nd record of name is:
SELECT ID, NAME from
(
SELECT rownum recno, ID, NAME from
(SELECT * from CUSTOMER ORDER by NAME)
WHERE rownum <= (2 + 3-1)
ORDER BY RowNum ASC
)
WHERE recno BETWEEN 2 and (2 + 3-1)
Attention:
The order in which the SQL clauses are executed:
1. From
2. WHERE
3. GROUP by
4. Having
5. SELECT
6. ORDER BY