select格式：

SELECT [ ALL | DISTINCT ] <字段表達(dá)式1[,<字段表達(dá)式2[,…]

FROM <表名1>,<表名2>[，…]

[WHERE <篩選擇條件表達(dá)式>]

[GROUP BY <分組表達(dá)式> [HAVING<分組條件表達(dá)式>]]

[ORDER BY <字段>[ASC | DESC]]

語(yǔ)句說(shuō)明：

[]方括號(hào)為可選項(xiàng)

[GROUP BY <分組表達(dá)式> [HAVING<分組條件表達(dá)式>]]

指將結(jié)果按<分組表達(dá)式>的值進(jìn)行分組，該值相等的記錄為一組，帶【HAVING】

短語(yǔ)則只有滿足指定條件的組才會(huì)輸出。

[ORDER BY <字段>[ASC | DESC]]

顯示結(jié)果要按<字段>值升序或降序進(jìn)行排序

練習(xí)：

1:表hkb_test_sore取出成績(jī)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：查詢兩個(gè)分?jǐn)?shù)一樣的記錄

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的區(qū)別：

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

綜合上面實(shí)例看個(gè)完整的實(shí)例

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中實(shí)現(xiàn)SELECT TOP N的方法

1.在ORACLE中實(shí)現(xiàn)SELECT TOP N

由于ORACLE不支持SELECT TOP語(yǔ)句，所以在ORACLE中經(jīng)常是用ORDER BY跟ROWNUM的組合來(lái)實(shí)現(xiàn)SELECT TOP N的查詢。

簡(jiǎn)單地說(shuō)，實(shí)現(xiàn)方法如下所示：

SELECT　列名1...列名n　FROM

(SELECT　列名1...列名n　FROM 表名 ORDER BY 列名1...列名n)

WHERE ROWNUM <= N(抽出記錄數(shù))

ORDER BY ROWNUM ASC

下面舉個(gè)例子簡(jiǎn)單說(shuō)明一下。

顧客表customer(id,name)有如下數(shù)據(jù)：

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的字母順抽出前三個(gè)顧客的SQL語(yǔ)句如下所示：

SELECT * FROM

(SELECT * FROM CUSTOMER ORDER BY NAME)

WHERE ROWNUM <= 3

ORDER BY ROWNUM ASC

輸出結(jié)果為：

ID NAME

08 eighth

05 fifth

01 first

2.在TOP N紀(jì)錄中抽出第M(M <= N)條記錄

在得到了TOP N的數(shù)據(jù)之后，為了抽出這N條記錄中的第M條記錄，我們可以考慮從ROWNUM著手。我們知道，ROWNUM是記錄表中數(shù)據(jù)編號(hào)的一個(gè)隱藏子段，所以可以在得到TOP N條記錄的時(shí)候同時(shí)抽出記錄的ROWNUM，然后再?gòu)倪@N條記錄中抽取記錄編號(hào)為M的記錄，即使我們希望得到的結(jié)果。

從上面的分析可以很容易得到下面的SQL語(yǔ)句。

SELECT 列名1...列名n FROM

(

SELECT ROWNUM RECNO, 列名1...列名nFROM

(SELECT 列名1...列名n FROM 表名 ORDER BY 列名1...列名n)

WHERE ROWNUM <= N(抽出記錄數(shù))

ORDER BY ROWNUM ASC

)

WHERE RECNO = M(M <= N)

同樣以上表的數(shù)據(jù)為基礎(chǔ)，那么得到以NAME的字母順排序的第二個(gè)顧客的信息的SQL語(yǔ)句應(yīng)該這樣寫：

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

結(jié)果則為：

ID NAME

05 fifth

3.抽出按某種方式排序的記錄集中的第N條記錄

在2的說(shuō)明中，當(dāng)M = N的時(shí)候，即為我們的標(biāo)題講的結(jié)果。實(shí)際上，2的做法在里面N>M的部分的數(shù)據(jù)是基本上不會(huì)用到的，我們僅僅是為了說(shuō)明方便而采用。

如上所述，則SQL語(yǔ)句應(yīng)為：

SELECT 列名1...列名n FROM

(

SELECT ROWNUM RECNO, 列名1...列名nFROM

(SELECT 列名1...列名n FROM 表名 ORDER BY 列名1...列名n)

WHERE ROWNUM <= N(抽出記錄數(shù))

ORDER BY ROWNUM ASC

)

WHERE RECNO = N

那么，2中的例子的SQL語(yǔ)句則為：

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

結(jié)果為：

ID NAME

05 fifth

4.抽出按某種方式排序的記錄集中的第M條記錄開(kāi)始的X條記錄

3里所講得僅僅是抽取一條記錄的情況，當(dāng)我們需要抽取多條記錄的時(shí)候，此時(shí)在2中的N的取值應(yīng)該是在N >= (M + X - 1)這個(gè)范圍內(nèi)，當(dāng)讓最經(jīng)濟(jì)的取值就是取等好的時(shí)候了的時(shí)候了。當(dāng)然最后的抽取條件也不是RECNO = N了，應(yīng)該是RECNO BETWEEN M AND (M + X - 1)了，所以隨之而來(lái)的SQL語(yǔ)句則為：

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)

同樣以上面的數(shù)據(jù)為例，則抽取NAME的字母順的第2條記錄開(kāi)始的3條記錄的SQL語(yǔ)句為：

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)

更多信息請(qǐng)查看IT技術(shù)專欄