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