Oracle版 WITH語(yǔ)句的使用

wghbeyond 2012-08-21

展開全文

說起WITH 語(yǔ)句,，除了那些第一次聽說WITH 語(yǔ)句的人，大部分人都覺得它是用來做
遞歸查詢的,。其實(shí)那只是它的一個(gè)用途而已,，它的本名正如我們標(biāo)題寫的那樣，叫做：公共
表表達(dá)式（Common Table Expression）,，從字面理解,，大家覺得它是用來干嘛的呢？
其實(shí),，它是用來定義臨時(shí)集合的,。

Sql代碼
WITH TEMP(ID,USER) AS(
SELECT ‘01’,’SAM’ FROM DUAL
UNION ALL
SELECT ‘02’,’MIKE’ FROM DUAL
UNION ALL
SELECT ‘03’,’TOM’ FROM DUAL
UNION ALL
SELECT ‘04’,’JANE’ FROM DUAL
)
SELECT * FROM TEMP;
WITH TEMP(ID,USER) AS(
SELECT ‘01’,’SAM’ FROM DUAL
UNION ALL
SELECT ‘02’,’MIKE’ FROM DUAL
UNION ALL
SELECT ‘03’,’TOM’ FROM DUAL
UNION ALL
SELECT ‘04’,’JANE’ FROM DUAL
)
SELECT * FROM TEMP;

WITH可以定義多個(gè)結(jié)果集，中間用逗號(hào)分隔,。
(這種寫法更加符合普通思維的邏輯,，寫出來的代碼更加容易理解)
WITH 語(yǔ)句是為復(fù)雜的查詢?yōu)樵O(shè)計(jì)的，的確是這樣的,，下面我們舉個(gè)復(fù)雜的例子,，想提高技術(shù)的朋友可千萬(wàn)不能錯(cuò)過?？紤]下面的情況：
Sql代碼
CREATE TABLE USER
(
NAME VARCHAR(20) NOT NULL,--姓名
DEGREE INTEGER NOT NULL,--學(xué)歷(1,、專科2,、本科3,、碩士4、博士)
STARTWORKDATE date NOT NULL,--入職時(shí)間
SALARY1 FLOAT NOT NULL,--基本工資
SALARY2 FLOAT NOT NULL--獎(jiǎng)金
);
CREATE TABLE USER
(
NAME VARCHAR(20) NOT NULL,--姓名
DEGREE INTEGER NOT NULL,--學(xué)歷(1、?？?,、本科3、碩士4,、博士)
STARTWORKDATE date NOT NULL,--入職時(shí)間
SALARY1 FLOAT NOT NULL,--基本工資
SALARY2 FLOAT NOT NULL--獎(jiǎng)金
);

假設(shè)現(xiàn)在讓你查詢一下那些1,、學(xué)歷是碩士或博士2、學(xué)歷相同,，入職年份也相同,，但是工資（基本工資+獎(jiǎng)金）卻比相同條件員工的平均工資低的員工。（哈哈,，可能是要漲工資），不知道你聽明白問題沒有,？該怎么查詢呢,？我們是這樣想的：
1、查詢學(xué)歷是碩士或博士的那些員工得到結(jié)果集1,，如下：
Sql代碼
SELECT NAME,DEGREE,YEAR(STARTWORKDATE) AS WORDDATE, SALARY1+SALARY2 AS SALARY FROM USER WHERE DEGREE IN (3,4),；
SELECT NAME,DEGREE,YEAR(STARTWORKDATE) AS WORDDATE, SALARY1+SALARY2 AS SALARY FROM USER WHERE DEGREE IN (3,4)；

2,、根據(jù)學(xué)歷和入職年份分組,，求平均工資得到結(jié)果集2，如下：
Sql代碼
SELECT DEGREE,YEAR(STARTWORKDATE) AS WORDDATE, AVG(SALARY1+SALARY2) AS AVG_SALARY
FROM USER WHERE DEGREE IN (3,4)
GROUP BY DEGREE,YEAR(STARTWORKDATE)
SELECT DEGREE,YEAR(STARTWORKDATE) AS WORDDATE, AVG(SALARY1+SALARY2) AS AVG_SALARY
FROM USER WHERE DEGREE IN (3,4)
GROUP BY DEGREE,YEAR(STARTWORKDATE)

3,、以學(xué)歷和入職年份為條件聯(lián)合兩個(gè)結(jié)果集,，查找工資<平均工資的員工,，以下是完整的SQL：
Sql代碼
WITH TEMP1(NAME,DEGREE,WORDDATE,SALARY) AS
(
SELECT NAME,DEGREE,YEAR(STARTWORKDATE) AS WORDDATE, SALARY1+SALARY2 AS SALARY FROM USER WHERE DEGREE IN (3,4)
),
TEMP2 (DEGREE,WORDDATE,AVG_SALARY) AS
(
SELECT DEGREE,YEAR(STARTWORKDATE) AS WORDDATE, AVG(SALARY1+SALARY2) AS AVG_SALARY
FROM USER WHERE DEGREE IN (3,4)
GROUP BY DEGREE,YEAR(STARTWORKDATE)
)
SELECT NAME FROM TEMP1, TEMP2 WHERE
TEMP1.DEGREE=TEMP2.DEGREE
AND TEMP1.WORDDATE=TEMP2.WORDDATE
AND SALARY<AVG_SALARY;
WITH TEMP1(NAME,DEGREE,WORDDATE,SALARY) AS
(
SELECT NAME,DEGREE,YEAR(STARTWORKDATE) AS WORDDATE, SALARY1+SALARY2 AS SALARY FROM USER WHERE DEGREE IN (3,4)
),
TEMP2 (DEGREE,WORDDATE,AVG_SALARY) AS
(
SELECT DEGREE,YEAR(STARTWORKDATE) AS WORDDATE, AVG(SALARY1+SALARY2) AS AVG_SALARY
FROM USER WHERE DEGREE IN (3,4)
GROUP BY DEGREE,YEAR(STARTWORKDATE)
)
SELECT NAME FROM TEMP1, TEMP2 WHERE
TEMP1.DEGREE=TEMP2.DEGREE
AND TEMP1.WORDDATE=TEMP2.WORDDATE
AND SALARY<AVG_SALARY;

查詢結(jié)果完全正確,，但我們還有改善的空間,，在查詢結(jié)果集2 的時(shí)候，我們是從user 表中取得數(shù)據(jù)的,。其實(shí)此時(shí)結(jié)果集1 已經(jīng)查詢出來了，我們完全可以從結(jié)果集1 中通過分組得到結(jié)果集2，而不用從uer 表中得到結(jié)果集2,，比較上面和下面的語(yǔ)句你就可以知道我說的是什么意思了！
Sql代碼
WITH TEMP1(NAME,DEGREE,WORDDATE,SALARY) AS
(
SELECT NAME,DEGREE,YEAR(STARTWORKDATE) AS WORDDATE, SALARY1+SALARY2 AS SALARY FROM USER WHERE DEGREE IN (3,4)
),
TEMP2 (DEGREE,WORDDATE,AVG_SALARY) AS
(
SELECT DEGREE,WORDDATE, AVG(SALARY) AS AVG_SALARY
FROM TEMP1
GROUP BY DEGREE,WORDDATE
)
SELECT NAME FROM TEMP1, TEMP2 WHERE
TEMP1.DEGREE=TEMP2.DEGREE
AND TEMP1.WORDDATE=TEMP2.WORDDATE
AND SALARY<AVG_SALARY;
WITH TEMP1(NAME,DEGREE,WORDDATE,SALARY) AS
(
SELECT NAME,DEGREE,YEAR(STARTWORKDATE) AS WORDDATE, SALARY1+SALARY2 AS SALARY FROM USER WHERE DEGREE IN (3,4)
),
TEMP2 (DEGREE,WORDDATE,AVG_SALARY) AS
(
SELECT DEGREE,WORDDATE, AVG(SALARY) AS AVG_SALARY
FROM TEMP1
GROUP BY DEGREE,WORDDATE
)
SELECT NAME FROM TEMP1, TEMP2 WHERE
TEMP1.DEGREE=TEMP2.DEGREE
AND TEMP1.WORDDATE=TEMP2.WORDDATE
AND SALARY<AVG_SALARY;

當(dāng)你看到這時(shí)，如果很好的理解了上面的內(nèi)容，我相信你會(huì)對(duì)WITH 語(yǔ)句有了一定的體會(huì),。然而WITH 語(yǔ)句能做的還不止這些，下面給大家介紹一下,，如何用WITH 語(yǔ)句做遞歸查詢,。

Sql代碼
CREATE TABLE BBS
(
PARENTID INTEGER NOT NULL,
ID INTEGER NOT NULL,
NAME VARCHAR(200) NOT NULL
);
insert into bbs (PARENTID,ID,NAME) values (0,1,'數(shù)據(jù)庫(kù)開發(fā)');
insert into bbs (PARENTID,ID,NAME) values (1,11,'DB2');
insert into bbs (PARENTID,ID,NAME) values (11,111,'DB2 文章1');
insert into bbs (PARENTID,ID,NAME) values (111,1111,'DB2 文章1 的評(píng)論1');
insert into bbs (PARENTID,ID,NAME) values (111,1112,'DB2 文章1 的評(píng)論2');
insert into bbs (PARENTID,ID,NAME) values (11,112,'DB2 文章2');
insert into bbs (PARENTID,ID,NAME) values (1,12,'Oracle');
insert into bbs (PARENTID,ID,NAME) values (0,2,'Java 技術(shù)');
COMMIT;
CREATE TABLE BBS
(
PARENTID INTEGER NOT NULL,
ID INTEGER NOT NULL,
NAME VARCHAR(200) NOT NULL
);
insert into bbs (PARENTID,ID,NAME) values (0,1,'數(shù)據(jù)庫(kù)開發(fā)');
insert into bbs (PARENTID,ID,NAME) values (1,11,'DB2');
insert into bbs (PARENTID,ID,NAME) values (11,111,'DB2 文章1');
insert into bbs (PARENTID,ID,NAME) values (111,1111,'DB2 文章1 的評(píng)論1');
insert into bbs (PARENTID,ID,NAME) values (111,1112,'DB2 文章1 的評(píng)論2');
insert into bbs (PARENTID,ID,NAME) values (11,112,'DB2 文章2');
insert into bbs (PARENTID,ID,NAME) values (1,12,'Oracle');
insert into bbs (PARENTID,ID,NAME) values (0,2,'Java 技術(shù)');
COMMIT;

那么,，現(xiàn)在讓你查詢一下DB2 的所有文章及評(píng)論,，怎么辦,？傳統(tǒng)的方法就
很難查詢了，這時(shí)候遞歸查詢就派上用場(chǎng)了,，如下：

Sql代碼
WITH TEMP(PARENTID,ID,NAME) AS
(
SELECT PARENTID,ID,NAME FROM BBS WHERE NAME='DB2'---語(yǔ)句1
UNION ALL---語(yǔ)句2
SELECT B.PARENTID,B.ID,B.NAME FROM BBS AS B, TEMP AS T WHERE B.PARENTI
D=T.ID---語(yǔ)句3
)
SELECT NAME FROM TEMP;---語(yǔ)句4
WITH TEMP(PARENTID,ID,NAME) AS
(
SELECT PARENTID,ID,NAME FROM BBS WHERE NAME='DB2'---語(yǔ)句1
UNION ALL---語(yǔ)句2
SELECT B.PARENTID,B.ID,B.NAME FROM BBS AS B, TEMP AS T WHERE B.PARENTI
D=T.ID---語(yǔ)句3
)
SELECT NAME FROM TEMP;---語(yǔ)句4

運(yùn)行后，我們發(fā)現(xiàn),，結(jié)果完全正確，那它到底是怎么運(yùn)行的呢,？下面我們?cè)敿?xì)講解一下,。
1、首先,，語(yǔ)句1 將會(huì)執(zhí)行,，它只執(zhí)行一次，作為循環(huán)的起點(diǎn),。得到結(jié)果集：DB2
2,、接著，將循環(huán)執(zhí)行語(yǔ)句3,，這里我們有必要詳細(xì)介紹一下,。
首先語(yǔ)句3 的意圖是什么呢？說白了,，它就是查找語(yǔ)句1產(chǎn)生結(jié)果集（DB2）的下一級(jí),，那么在目錄樹中DB2 的下一級(jí)是什么呢？是‘DB2 文章1’和‘DB2 文章2’,，并且把查詢到的結(jié)果集作為下一次循環(huán)的起點(diǎn)，然后查詢它們的下一級(jí),，直到?jīng)]有下一級(jí)為止,。
怎么樣,？還沒明白？哈哈,，不要緊，我們一步一步來：
首先,，語(yǔ)句1 產(chǎn)生結(jié)果集：DB2，作為循環(huán)的起點(diǎn),，把它和BBS 表關(guān)聯(lián)來查找它的下一級(jí),，查詢后的結(jié)果為：‘DB2 文章1’和‘DB2 文章2’接著,，把上次的查詢結(jié)果（也就是‘DB2 文章1’和‘DB2 文章2’）和BBS 表關(guān)聯(lián)來查找它
們的下一級(jí),，查詢后的結(jié)果為：‘DB2 文章1 的評(píng)論1’ 和‘DB2 文章1 的評(píng)論2’,。然后，在把上次的查詢結(jié)果（也就是‘DB2 文章1 的評(píng)論1’ 和‘DB2 文章1 的評(píng)論2’）和BBS 表關(guān)聯(lián)來查找它們的下一級(jí),，此時(shí)，沒有結(jié)果返回,，循環(huán)結(jié)束。
3,、第三，將執(zhí)行語(yǔ)句2,，將所有的結(jié)果集放在一起,，最終得到temp 結(jié)果集,。
4、最后,，我們通過語(yǔ)句4 從temp 臨時(shí)集合中得到我們期望的查詢結(jié)果。怎么樣,，這回理解了吧，如果還沒有理解,，那么我也無(wú)能為力了。
需要特別提醒的是
1,、一定要注意語(yǔ)句3 的關(guān)聯(lián)條件，否則很容易就寫成死循環(huán)了,。
2、語(yǔ)句2必須是UNION ALL

接下來,，如果做如此更改后,，再次執(zhí)行WITH 遞歸語(yǔ)句
Sql代碼
UPDATE BBS SET ID = 1 WHERE PARENTID = 1;
UPDATE BBS SET ID = 1 WHERE PARENTID = 1;

再次執(zhí)行則會(huì)出現(xiàn)以下錯(cuò)誤：
ERROR:ORA-32044:執(zhí)行遞歸WITH查詢時(shí)檢測(cè)到循環(huán)

這時(shí)候，可以使用CYCLE語(yǔ)句忽略循環(huán)導(dǎo)致的錯(cuò)誤,，如下：
Sql代碼
WITH TEMP(PARENTID,ID,NAME) AS
(
SELECT PARENTID,ID,NAME FROM BBS WHERE NAME='DB2'---語(yǔ)句1
UNION ALL---語(yǔ)句2
SELECT B.PARENTID,B.ID,B.NAME FROM BBS AS B, TEMP AS T WHERE B.PARENTI
D=T.ID---語(yǔ)句3
)
CYCLE ID SET DUP_IND TO 'Y' DEFAULT 'N'
SELECT NAME FROM TEMP;---語(yǔ)句4
WITH TEMP(PARENTID,ID,NAME) AS
(
SELECT PARENTID,ID,NAME FROM BBS WHERE NAME='DB2'---語(yǔ)句1
UNION ALL---語(yǔ)句2
SELECT B.PARENTID,B.ID,B.NAME FROM BBS AS B, TEMP AS T WHERE B.PARENTI
D=T.ID---語(yǔ)句3
)
CYCLE ID SET DUP_IND TO 'Y' DEFAULT 'N'
SELECT NAME FROM TEMP;---語(yǔ)句4

此時(shí)我們得到多了一列出來,，DUP_IND
（PS.需要注意的是，這個(gè)結(jié)果和使用CONNECT BY NOCYCLE得到的結(jié)果并不一致,，ID為1的記錄會(huì)出現(xiàn)兩次,。后面會(huì)降到CONNECT BY語(yǔ)句的使用）

DUP_IND為’Y’的時(shí)候,，表示出現(xiàn)了死循環(huán)。

同時(shí)CYCLE可以判斷在在同一葉子上是否出現(xiàn)重復(fù)的值,。
如果執(zhí)行：
Sql代碼
insert into bbs (PARENTID,ID,NAME) values (11,112,'DB2 文章1');
insert into bbs (PARENTID,ID,NAME) values (11,112,'DB2 文章1');

再次執(zhí)行帶CYCLE的遞歸查詢語(yǔ)句,。
也會(huì)發(fā)現(xiàn)有一個(gè)’Y’在上面，因?yàn)楹竺娴摹癉B2 文章1”內(nèi)容重復(fù)了,。

但是,，如果執(zhí)行的插入數(shù)據(jù)語(yǔ)句是：
Sql代碼
insert into bbs (PARENTID,ID,NAME) values (111,1113,'DB2 文章1');

本站是提供個(gè)人知識(shí)管理的網(wǎng)絡(luò)存儲(chǔ)空間，所有內(nèi)容均由用戶發(fā)布,，不代表本站觀點(diǎn)。請(qǐng)注意甄別內(nèi)容中的聯(lián)系方式,、誘導(dǎo)購(gòu)買等信息,，謹(jǐn)防詐騙。如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容,，請(qǐng)點(diǎn)擊一鍵舉報(bào),。

轉(zhuǎn)藏 分享

QQ空間 QQ好友新浪微博微信

獻(xiàn)花（0） +1

來自： wghbeyond > 《oracle_sql》

舉報(bào)/認(rèn)領(lǐng)