說(shuō)起WITH 語(yǔ)句，除了那些第一次聽說(shuō)WITH語(yǔ)句的人，大部分人都覺得它是用來(lái)做遞歸查詢的。其實(shí)那只是它的一個(gè)用途而已，它的本名正如我們標(biāo)題寫的那樣，叫做：公共表表達(dá)式（Common Table Expression），從字面理解，大家覺得它是用來(lái)干嘛的呢？其實(shí)，它是用來(lái)定義臨時(shí)集合的。啊？VALUES語(yǔ)句不是用來(lái)定義臨時(shí)集合的嗎？怎么WITH語(yǔ)句也用來(lái)定義臨時(shí)集合呢？它們有什么區(qū)別呢？
VALUES語(yǔ)句是用明確的值來(lái)定義臨時(shí)集合的，如下：
VALUES (1,2), (1,3),(2,1)
VALUES (1,2), (1,3),(2,1)
WITH語(yǔ)句是用查詢(也就是select語(yǔ)句)來(lái)定義臨時(shí)集合的，從這個(gè)角度講，有點(diǎn)像視圖，不過(guò)不是視圖，大家千萬(wàn)別誤解。如下：
CREATE TABLE USER (
NAME VARCHAR(20) NOT NULL,---姓名
SEX INTEGER,---性別（1、男 2、女）
BIRTHDAY DATE---生日
);
CREATE TABLE USER (
NAME VARCHAR(20) NOT NULL,---姓名
SEX INTEGER,---性別（1、男 2、女）
BIRTHDAY DATE---生日
);
WITH TEST(NAME_TEST, BDAY_TEST) AS
(
SELECT NAME,BIRTHDAY FROM USER--語(yǔ)句1
)
SELECT NAME_TEST FROM TEST WHERE BDAY_TEST='1949-10-1';--語(yǔ)句2
WITH TEST(NAME_TEST, BDAY_TEST) AS
(
SELECT NAME,BIRTHDAY FROM USER--語(yǔ)句1
)
SELECT NAME_TEST FROM TEST WHERE BDAY_TEST='1949-10-1';--語(yǔ)句2
下面我們來(lái)解釋一下，首先語(yǔ)句1執(zhí)行，它會(huì)產(chǎn)生一個(gè)有兩列（NAME,BIRTHDAY）的結(jié)果集；接著，我們將這個(gè)結(jié)果集命名為test，并且將列名重命名為NAME_TEST, BDAY_TEST；最后我們執(zhí)行語(yǔ)句2，從這個(gè)臨時(shí)集合中找到生日是1949-10-1，也就是共和國(guó)的同齡人。
怎么樣？如果你感覺不好理解，請(qǐng)仔細(xì)的分析一下上面的語(yǔ)句。下面我們舉個(gè)VALUES語(yǔ)句和WITH語(yǔ)句結(jié)合使用的例子，如下：
WITH TEST(NAME_TEST, BDAY_TEST) AS
(
VALUES ('張三','1997-7-1'),('李四','1949-10-1')
)
SELECT NAME_TEST FROM TEST WHERE BDAY_TEST='1949-10-1'
WITH TEST(NAME_TEST, BDAY_TEST) AS
(
VALUES ('張三','1997-7-1'),('李四','1949-10-1')
)
SELECT NAME_TEST FROM TEST WHERE BDAY_TEST='1949-10-1'
從上面的介紹和WITH語(yǔ)句不為大多數(shù)人所熟悉可以猜測(cè)，WITH語(yǔ)句是為復(fù)雜的查詢?yōu)樵O(shè)計(jì)的，的確是這樣的，下面我們舉個(gè)復(fù)雜的例子，想提高技術(shù)的朋友可千萬(wàn)不能錯(cuò)過(guò)。考慮下面的情況：
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、專科 2、本科 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)金）卻比相同條件員工的平均工資低的員工。（哈哈，可能是要漲工資），不知道你聽明白問(wèn)題沒有？該怎么查詢呢？我們是這樣想的：
1、查詢學(xué)歷是碩士或博士的那些員工得到結(jié)果集1，如下：
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，如下：
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：
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)查詢出來(lái)了，我們完全可以從結(jié)果集1中通過(guò)分組得到結(jié)果集2，而不用從uer表中得到結(jié)果集2，比較上面和下面的語(yǔ)句你就可以知道我說(shuō)的是什么意思了！
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;
可能有些朋友會(huì)說(shuō)，我不用WITH語(yǔ)句也可以查出來(lái)，的確是這樣，如下：
SELECT U.NAME FROM USER AS U,
(
SELECT DEGREE,YEAR(STARTWORKDATE) AS WORDDATE, AVG(SALARY1+SALARY2) AS AVG_SALARY
FROM USER WHERE DEGREE IN (3,4)
GROUP BY DEGREE,YEAR(STARTWORKDATE)
) AS G
WHERE U.DEGREE=G.DEGREE
AND YEAR(U.STARTWORKDATE)=G.WORDDATE
AND (SALARY1+SALARY2)<G.AVG_SALARY;
SELECT U.NAME FROM USER AS U,
(
SELECT DEGREE,YEAR(STARTWORKDATE) AS WORDDATE, AVG(SALARY1+SALARY2) AS AVG_SALARY
FROM USER WHERE DEGREE IN (3,4)
GROUP BY DEGREE,YEAR(STARTWORKDATE)
) AS G
WHERE U.DEGREE=G.DEGREE
AND YEAR(U.STARTWORKDATE)=G.WORDDATE
AND (SALARY1+SALARY2)<G.AVG_SALARY;
那使用WITH 和不使用 WITH，這兩種寫法有什么區(qū)別呢？一般情況下這兩種寫法在性能上不會(huì)有太大差異，但是，
1、當(dāng)USER表的記錄很多
2、碩士或博士（DEGREE IN (3,4)）在USER表中的比例很少
當(dāng)滿足以上條件時(shí)，這兩種寫法在性能的差異將會(huì)顯現(xiàn)出來(lái)，為什么呢？因?yàn)椴皇褂肳ITH寫法的語(yǔ)句訪問(wèn)了2次USER表，如果DEGREE 字段又沒有索引，性能差異將會(huì)非常明顯。
當(dāng)你看到這時(shí)，如果很好的理解了上面的內(nèi)容，我相信你會(huì)對(duì)WITH語(yǔ)句有了一定的體會(huì)。然而WITH語(yǔ)句能做的還不止這些，下面給大家介紹一下，如何用WITH語(yǔ)句做遞歸查詢。遞歸查詢的一個(gè)典型的例子是對(duì)樹狀結(jié)構(gòu)的表進(jìn)行查詢，考慮如下的情況：
論壇首頁(yè)
--數(shù)據(jù)庫(kù)開發(fā)
----DB2
------DB2 文章1
--------DB2 文章1 的評(píng)論1
--------DB2 文章1 的評(píng)論2
------DB2 文章2
----Oracle
--Java技術(shù)
論壇首頁(yè)
--數(shù)據(jù)庫(kù)開發(fā)
----DB2
------DB2 文章1
--------DB2 文章1 的評(píng)論1
--------DB2 文章1 的評(píng)論2
------DB2 文章2
----Oracle
--Java技術(shù)
以上是一個(gè)論壇的典型例子，下面我們新建一個(gè)表來(lái)存儲(chǔ)以上信息。
CREATE TABLE BBS
(
PARENTID INTEGER NOT NULL,
ID INTEGER NOT NULL,
NAME VARCHAR(200) NOT NULL---板塊、文章、評(píng)論等。
);
insert into bbs (PARENTID,ID,NAME) values
(0,0,'論壇首頁(yè)'),
(0,1,'數(shù)據(jù)庫(kù)開發(fā)'),
(1,11,'DB2'),
(11,111,'DB2 文章1'),
(111,1111,'DB2 文章1 的評(píng)論1'),
(111,1112,'DB2 文章1 的評(píng)論2'),
(11,112,'DB2 文章2'),
(1,12,'Oracle'),
(0,2,'Java技術(shù)');
CREATE TABLE BBS
(
PARENTID INTEGER NOT NULL,
ID INTEGER NOT NULL,
NAME VARCHAR(200) NOT NULL---板塊、文章、評(píng)論等。
);
insert into bbs (PARENTID,ID,NAME) values
(0,0,'論壇首頁(yè)'),
(0,1,'數(shù)據(jù)庫(kù)開發(fā)'),
(1,11,'DB2'),
(11,111,'DB2 文章1'),
(111,1111,'DB2 文章1 的評(píng)論1'),
(111,1112,'DB2 文章1 的評(píng)論2'),
(11,112,'DB2 文章2'),
(1,12,'Oracle'),
(0,2,'Java技術(shù)');
現(xiàn)在萬(wàn)事兼?zhèn)淞耍覀冮_始查詢吧。假設(shè)現(xiàn)在讓你查詢一下‘DB2 文章1’的所有評(píng)論，有人說(shuō)，這還不簡(jiǎn)單，如下這樣就可以了。
SELECT * FROM BBS WHERE PARENTID=(SELECT ID FROM BBS WHERE NAME='DB2');
SELECT * FROM BBS WHERE PARENTID=(SELECT ID FROM BBS WHERE NAME='DB2');
答案完全正確。那么，現(xiàn)在讓你查詢一下DB2的所有文章及評(píng)論，怎么辦？傳統(tǒng)的方法就很難查詢了，這時(shí)候遞歸查詢就派上用場(chǎng)了，如下：
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.PARENTID=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.PARENTID=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的意圖是什么呢？說(shuō)白了，它就是查找語(yǔ)句1產(chǎn)生結(jié)果集（DB2）的下一級(jí)，那么在目錄樹中DB2的下一級(jí)是什么呢？是‘DB2 文章1’和‘DB2 文章2’，并且把查詢到的結(jié)果集作為下一次循環(huán)的起點(diǎn)，然后查詢它們的下一級(jí)，直到?jīng)]有下一級(jí)為止。
怎么樣？還沒明白？哈哈，不要緊，我們一步一步來(lái)：
首先，語(yǔ)句1產(chǎn)生結(jié)果集：DB2，作為循環(huán)的起點(diǎn)，把它和BBS表關(guān)聯(lián)來(lái)查找它的下一級(jí)，查詢后的結(jié)果為：‘DB2 文章1’和‘DB2 文章2’
接著，把上次的查詢結(jié)果（也就是‘DB2 文章1’和‘DB2 文章2’）和BBS表關(guān)聯(lián)來(lái)查找它們的下一級(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)來(lái)查找它們的下一級(jí)，此時(shí)，沒有結(jié)果返回，循環(huán)結(jié)束。
3、第三，將執(zhí)行語(yǔ)句2，將所有的結(jié)果集放在一起，最終得到temp結(jié)果集。
4、最后，我們通過(guò)語(yǔ)句4 從temp臨時(shí)集合中得到我們期望的查詢結(jié)果。
怎么樣，這回理解了吧，如果還沒有理解，那么我也無(wú)能為力了。需要特別提醒的是
1、一定要注意語(yǔ)句3的關(guān)聯(lián)條件，否則很容易就寫成死循環(huán)了。
2、語(yǔ)句2必須是UNION ALL
最后請(qǐng)大家猜想一下，把語(yǔ)句1的where子句去掉，將會(huì)產(chǎn)生什么樣的結(jié)果呢？
---更多參見：DB2 SQL 精萃
----聲明：轉(zhuǎn)載請(qǐng)注明出處。
----last updated at 2010.1.27
----written by wave at 2009.9.27
----end
本文來(lái)自CSDN博客，轉(zhuǎn)載請(qǐng)標(biāo)明出處：http://blog.csdn.net/shangboerds/archive/2009/09/27/4601023.aspx