select格式：
SELECT [ ALL | DISTINCT ] <字段表達式1[,<字段表達式2[,…]
FROM <表名1>,<表名2>[，…]
[WHERE <篩選擇條件表達式>]
[GROUP BY <分組表達式> [HAVING<分組條件表達式>]]
[ORDER BY <字段>[ASC | DESC]]
語句說明：
[]方括號為可選項
[GROUP BY <分組表達式> [HAVING<分組條件表達式>]]
指將結果按<分組表達式>的值進行分組，該值相等的記錄為一組，帶【HAVING】
短語則只有滿足指定條件的組才會輸出。
[ORDER BY <字段>[ASC | DESC]]
顯示結果要按<字段>值升序或降序進行排序
練習：
1:表hkb_test_sore取出成績sore前5名的記錄，
2:取第5名的記錄
1，答案select a.sore_id, a.sore
from (select * from hkb_test_sore order by sore desc) a
where rownum <=5
2，答案select a.sore_id, a.sore
from (select * from hkb_test_sore order by sore desc) a
where rownum <=5
minus
select a.sore_id, a.sore
from (select * from hkb_test_sore order by sore desc) a
where rownum <=4;
3：查詢兩個分數一樣的記錄
select *
from hkb_test_sore a
where a.sore = (select sore
from hkb_test_sore a
group by a.sore
having count(a.sore) = 2);
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
綜合上面實例看個完整的實例
SQL>
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL primary key,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ('01','Jason', 'Martin', to_date('19960725','YYYYMMDD'), to_date('20060725','YYYYMMDD'), 1234.56, 'Toronto', 'Programmer')
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('02','Alison', 'Mathews', to_date('19760321','YYYYMMDD'), to_date('19860221','YYYYMMDD'), 6661.78, 'Vancouver','Tester')
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('03','James', 'Smith', to_date('19781212','YYYYMMDD'), to_date('19900315','YYYYMMDD'), 6544.78, 'Vancouver','Tester')
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('04','Celia', 'Rice', to_date('19821024','YYYYMMDD'), to_date('19990421','YYYYMMDD'), 2344.78, 'Vancouver','Manager')
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('05','Robert', 'Black', to_date('19840115','YYYYMMDD'), to_date('19980808','YYYYMMDD'), 2334.78, 'Vancouver','Tester')
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('06','Linda', 'Green', to_date('19870730','YYYYMMDD'), to_date('19960104','YYYYMMDD'), 4322.78,'New York', 'Tester')
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('07','David', 'Larry', to_date('19901231','YYYYMMDD'), to_date('19980212','YYYYMMDD'), 7897.78,'New York', 'Manager')
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('08','James', 'Cat', to_date('19960917','YYYYMMDD'), to_date('20020415','YYYYMMDD'), 1232.78,'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
---- -------------------- -------------------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 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
---- -------------------- --------------------
01 Jason Martin
02 Alison Mathews
03 James Smith
04 Celia Rice
05 Robert Black
06 Linda Green
07 David Larry
08 James Cat
8 rows selected.
在ORACLE中實現SELECT TOP N的方法
1.在ORACLE中實現SELECT TOP N
由于ORACLE不支持SELECT TOP語句，所以在ORACLE中經常是用ORDER BY跟ROWNUM的組合來實現SELECT TOP N的查詢。
簡單地說，實現方法如下所示：
SELECT 列名1...列名n FROM
(SELECT 列名1...列名n FROM 表名 ORDER BY 列名1...列名n)
WHERE ROWNUM <= N(抽出記錄數)
ORDER BY ROWNUM ASC
下面舉個例子簡單說明一下。
顧客表customer(id,name)有如下數據：
ID NAME
01 first
02 Second
03 third
04 forth
05 fifth
06 sixth
07 seventh
08 eighth
09 ninth
10 tenth
11 last
則按NAME的字母順抽出前三個顧客的SQL語句如下所示：
SELECT * FROM
(SELECT * FROM CUSTOMER ORDER BY NAME)
WHERE ROWNUM <= 3
ORDER BY ROWNUM ASC
輸出結果為：
ID NAME
08 eighth
05 fifth
01 first
2.在TOP N紀錄中抽出第M(M <= N)條記錄
在得到了TOP N的數據之后，為了抽出這N條記錄中的第M條記錄，我們可以考慮從ROWNUM著手。我們知道，ROWNUM是記錄表中數據編號的一個隱藏子段，所以可以在得到TOP N條記錄的時候同時抽出記錄的ROWNUM，然后再從這N條記錄中抽取記錄編號為M的記錄，即使我們希望得到的結果。
從上面的分析可以很容易得到下面的SQL語句。
SELECT 列名1...列名n FROM
(
SELECT ROWNUM RECNO, 列名1...列名nFROM
(SELECT 列名1...列名n FROM 表名 ORDER BY 列名1...列名n)
WHERE ROWNUM <= N(抽出記錄數)
ORDER BY ROWNUM ASC
)
WHERE RECNO = M(M <= N)
同樣以上表的數據為基礎，那么得到以NAME的字母順排序的第二個顧客的信息的SQL語句應該這樣寫：
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
結果則為：
ID NAME
05 fifth
3.抽出按某種方式排序的記錄集中的第N條記錄
在2的說明中，當M = N的時候，即為我們的標題講的結果。實際上，2的做法在里面N>M的部分的數據是基本上不會用到的，我們僅僅是為了說明方便而采用。
如上所述，則SQL語句應為：
SELECT 列名1...列名n FROM
(
SELECT ROWNUM RECNO, 列名1...列名nFROM
(SELECT 列名1...列名n FROM 表名 ORDER BY 列名1...列名n)
WHERE ROWNUM <= N(抽出記錄數)
ORDER BY ROWNUM ASC
)
WHERE RECNO = N
那么，2中的例子的SQL語句則為：
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
結果為：
ID NAME
05 fifth
4.抽出按某種方式排序的記錄集中的第M條記錄開始的X條記錄
3里所講得僅僅是抽取一條記錄的情況，當我們需要抽取多條記錄的時候，此時在2中的N的取值應該是在N >= (M + X - 1)這個范圍內，當讓最經濟的取值就是取等好的時候了的時候了。當然最后的抽取條件也不是RECNO = N了，應該是RECNO BETWEEN M AND (M + X - 1)了，所以隨之而來的SQL語句則為：
SELECT 列名1...列名n FROM
(
SELECT ROWNUM RECNO, 列名1...列名nFROM
(
SELECT 列名1...列名n FROM 表名 ORDER BY 列名1...列名n)
WHERE ROWNUM <= N (N >= (M + X - 1))
ORDER BY ROWNUM ASC
)
WHERE RECNO BETWEEN M AND (M + X - 1)
同樣以上面的數據為例，則抽取NAME的字母順的第2條記錄開始的3條記錄的SQL語句為：
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)
更多信息請查看IT技術專欄
版權所有:易賢網
公眾號
事業單位
當前位置：
公考類
招聘類
各類考試