IT技術互動交流平臺

DB2with的定義與用法

來源:IT165收集  發布日期:2016-03-25 23:14:42
With定義與用法
-------部分內容為轉載并經整理處理--------------------- 1.with理解與基本用法 說起WITH 語句,除了那些第一次聽說WITH語句的人,大部分人都覺得它是用來做遞歸查詢的。其實那只是它的一個用途而已, 它的本名正如我們標題寫的那樣,叫做:公共表表達式(Common Table Expression),從字面理解,大家覺得它是用來干嘛的呢? 其實,它是用來定義臨時集合的。?VALUES語句不是用來定義臨時集合的嗎?怎么WITH語句也用來定義臨時集合呢?它們有什么區別呢?
VALUES語句是用明確的值來定義臨時集合的,如下: values (1,2), (1,3),(2,1) WITH語句是用查詢(也就是select語句)來定義臨時集合的,從這個角度講,有點像視圖,不過不是視圖,大家千萬別誤解。例如下:
--建表 DROP TABLE USER; CREATE TABLE USER ( NAME VARCHAR(20) NOT NULL,---姓名 SEX INTEGER,---性別(1、男 2、女) BIRTHDAY DATE---生日 ); --插數據 insert into user (name,sex,birthday) values ('zhangshan','1','1990-1-1'); insert into user (name,sex,birthday) values ('lisi','2','1991-1-1'); insert into user (name,sex,birthday) values ('wangwu','1','1992-1-1'); insert into user (name,sex,birthday) values ('sunliu','2','1949-10-1'); insert into user (name,sex,birthday) values ('tianqi','1','1994-1-1'); insert into user (name,sex,birthday) values ('zhaoba','2','1995-1-1');

WITH TEST(NAME_TEST, BDAY_TEST) AS --test是括號中查詢出來的結果集命名,后接重命名列 ( SELECT NAME,BIRTHDAY FROM USER--語句1 ) SELECT NAME_TEST FROM TEST WHERE BDAY_TEST='1949-10-1';--語句2
下面我們來解釋一下,首先語句1執行,它會產生一個有兩列(NAME,BIRTHDAY)的結果集;接著,我們將這個結果集命名為test,并且將列名重命名為NAME_TEST, BDAY_TEST; 最后我們執行語句2,從這個臨時集合中找到生日是1949-10-1,也就是共和國的同齡人。
怎么樣?如果你感覺不好理解,請仔細的分析一下上面的語句。下面我們舉個VALUES語句和WITH語句結合使用的例子,如下:
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語句不為大多數人所熟悉可以猜測,WITH語句是為復雜的查詢為設計的,的確是這樣的, 下面我們舉個復雜的例子,想提高技術的朋友可千萬不能錯過?紤]下面的情況:
--建表 DROP TABLE USER2; CREATE TABLE USER2 ( NAME VARCHAR(20) NOT NULL,--姓名 DEGREE INTEGER NOT NULL,--學歷(1、? 2、本科 3、碩士 4、博士) STARTWORKDATE date NOT NULL,--入職時間 SALARY1 FLOAT NOT NULL,--基本工資 SALARY2 FLOAT NOT NULL--獎金 );
--插數據 insert into user2 (name,degree,startworkdate,salary1,salary2) values ('zhangsan',1,'1995-1-1',10000.00,1600.00); insert into user2 (name,degree,startworkdate,salary1,salary2) values ('lisi',2,'1996-1-1',5000.00,1500.00); insert into user2 (name,degree,startworkdate,salary1,salary2) values ('wangwu',3,'1997-1-1',6000.00,1400.00); insert into user2 (name,degree,startworkdate,salary1,salary2) values ('sunliu',4,'1998-1-1',7000.00,1300.00); insert into user2 (name,degree,startworkdate,salary1,salary2) values ('tianqi',2,'1999-1-1','7000','1300'); insert into user2 (name,degree,startworkdate,salary1,salary2) values ('zhaoba',1,'2000-1-1',9000,1400); insert into user2 (name,degree,startworkdate,salary1,salary2) values ('qianjiu',3,'1997-1-1',2000,1000); insert into user2 (name,degree,startworkdate,salary1,salary2) values ('dushe',4,'1992-1-1',3000,1000); select * from user2;
假設現在讓你查詢一下那些 1、學歷是碩士或博士 2、學歷相同,入職年份也相同,但是工資(基本工資+獎金)卻比相同條件員工的平均工資低的員工。 哈哈,可能是要漲工資),不知道你聽明白問題沒有?該怎么查詢呢?我們是這樣想的:
1、查詢學歷是碩士或博士的那些員工得到結果集1,如下: SELECT NAME,DEGREE,YEAR(STARTWORKDATE) AS WORDDATE,SALARY1+SALARY2 AS SALARY FROM USER2 WHERE DEGREE IN (3,4);
2、根據學歷和入職年份分組,求平均工資 得到結果集2,如下: SELECT DEGREE,YEAR(STARTWORKDATE) AS WORDDATE, AVG(SALARY1+SALARY2) AS AVG_SALARY FROM USER2 WHERE DEGREE IN (3,4) GROUP BY DEGREE,YEAR(STARTWORKDATE);
3、以學歷和入職年份為條件 聯合兩個結果集,查找工資<平均工資 的員工,以下是完整的SQL: WITH TEMP1(NAME,DEGREE,WORDDATE,SALARY) AS ( SELECT NAME,DEGREE,YEAR(STARTWORKDATE) AS WORDDATE, SALARY1+SALARY2 AS SALARY FROM USER2 WHERE DEGREE IN (3,4) ), TEMP2 (DEGREE,WORDDATE,AVG_SALARY) AS ( SELECT DEGREE,YEAR(STARTWORKDATE) AS WORDDATE, AVG(SALARY1+SALARY2) AS AVG_SALARY FROM USER2 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;
查詢結果完全正確,但我們還有改善的空間,在查詢結果集2的時候,我們是從user表中取得數據的。 其實此時結果集1已經查詢出來了,我們完全可以從結果集1中通過分組得到結果集2, 而不用從uer表中得到結果集2,比較上面和下面的語句你就可以知道我說的是什么意思了! WITH TEMP1(NAME,DEGREE,WORDDATE,SALARY) AS ( SELECT NAME,DEGREE,YEAR(STARTWORKDATE) AS WORDDATE, SALARY1+SALARY2 AS SALARY FROM USER2 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 USER2 AS U, ( SELECT DEGREE,YEAR(STARTWORKDATE) AS WORDDATE, AVG(SALARY1+SALARY2) AS AVG_SALARY FROM USER2 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,這兩種寫法有什么區別呢?一般情況下這兩種寫法在性能上不會有太大差異,但是, 1、當USER表的記錄很多 2、碩士或博士(DEGREE IN (3,4))在USER表中的比例很少
當滿足以上條件時,這兩種寫法在性能的差異將會顯現出來,為什么呢?因為不使用WITH寫法的語句訪問了2次USER表, 如果DEGREE 字段又沒有索引,性能差異將會非常明顯。

2.with的遞歸應用一 當你看到這時,如果很好的理解了上面的內容,我相信你會對WITH語句有了一定的體會。然而WITH語句能做的還不止這些, 下面給大家介紹一下,如何用WITH語句做遞歸查詢。遞歸查詢的一個典型的例子是對樹狀結構的表進行查詢,考慮如下的情況:
01.論壇首頁 02.--數據庫開發 03.----DB2 04.------DB2 文章1 05.--------DB2 文章1 的評論1 06.--------DB2 文章1 的評論2 07.------DB2 文章2 08.----Oracle 09.--Java技術 以上是一個論壇的典型例子,下面我們新建一個表來存儲以上信息。 drop table BBS; 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,'數據庫開發'), (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技術'); 現在萬事兼備了,我們開始查詢吧。假設現在讓你查詢一下‘DB2 文章1’的所有評論,有人說,這還不簡單,如下這樣就可以了。 SELECT * FROM BBS WHERE PARENTID=(SELECT ID FROM BBS WHERE NAME='DB2 文章1'); 答案完全正確。那么,現在讓你查詢一下DB2的所有文章及評論,怎么辦?傳統的方法就很難查詢了,這時候遞歸查詢就派上用場了,如下: 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 子句內的第一個 SELECT 語句是初始化表。它只執行一次。它的結果形成虛擬表的初始內容以作為遞歸的種子。在上面的示例中,種子是 'NAME' 為 DB2的一行或多行。
第二個 SELECT 語句執行多次。將種子作為輸入傳遞給第二個 SELECT 語句以產生下一個行集合。將結果添加(UNION ALL)到虛擬表的當前內容中,并放回到其中以形成用于下一次傳遞的輸入。只要有行產生,這個過程就會繼續。
運行后,我們發現,結果完全正確,那它到底是怎么運行的呢?下面我們詳細講解一下。 1、首先,語句1將會執行,它只執行一次,作為循環的起點。得到結果集:DB2 2、接著,將循環執行語句3,這里我們有必要詳細介紹一下。 首先語句3的意圖是什么呢?說白了,它就是查找語句1產生結果集(DB2)的下一級,那么在目錄樹中DB2的下一級是什么呢?是‘DB2 文章1’和‘DB2 文章2’, 并且把查詢到的結果集作為下一次循環的起點,然后查詢它們的下一級,直到沒有下一級為止。
怎么樣?還沒明白?哈哈,不要緊,我們一步一步來: 首先,語句1產生結果集:DB2,作為循環的起點,把它和BBS表關聯來查找它的下一級,查詢后的結果為:‘DB2 文章1’和‘DB2 文章2’ 接著,把上次的查詢結果(也就是‘DB2 文章1’和‘DB2 文章2’)和BBS表關聯來查找它們的下一級,查詢后的結果為:‘DB2 文章1 的評論1’ 和 ‘DB2 文章1 的評論2’。 然后,在把上次的查詢結果(也就是‘DB2 文章1 的評論1’ 和 ‘DB2 文章1 的評論2’)和BBS表關聯來查找它們的下一級,此時,沒有結果返回,循環結束。 3、第三,將執行語句2,將所有的結果集放在一起,最終得到temp結果集。 4、最后,我們通過語句4 從temp臨時集合中得到我們期望的查詢結果。
需要特別提醒的是 1、一定要注意語句3的關聯條件,否則很容易就寫成死循環了。 2、語句2必須是 UNION ALL 最后請大家猜想一下,把語句1的where子句去掉,將會產生什么樣的結果呢?去掉where后將全是死循環,因為每次查出的結果集都是全查的記錄,而且永遠都是。
3.with遞歸應用二,行轉列 --1.建表 drop table zxt_test create table zxt_test ( id varchar(10), ivalue varchar(20), iname varchar(20) ); commit; select * from zxt_test; ----------- ---2.插入測試語句 insert into zxt_test values('1','aa','x'),('2','bb','x'),('3','bb','x'),('1','bb','y'),('2','bb','y'),('3','bb','y'); commit;
with s as ( --這里是用iname來分區,id來排序。如果表沒有這樣序號分明的id字段,可以用rowNum()生成序號 select row_number()over(partition by iname order by id) id1, row_number()over(partition by iname order by id) id2, ivalue,iname from zxt_test ), t(iname,id1,id2,ivalue) as ( select iname,id1,id2,cast(ivalue as varchar(100)) from s where id1 =1 and id2=1 --語句1 union all select t.iname,t.id1+1,t.id2,cast(s.ivalue||','||t.ivalue as varchar(100)) --語句2 from s, t where s.id2=t.id1+1 and t.iname = s.iname ) --where s.iname = t.iname可以去掉,不影響 select iname,ivalue from t where t.id1= (select max(id1) from s where s.iname = t.iname); --語句3 結果集s DB2 <wbr>with的定義與用法

臨時表t里面,首先執行語句1.取得根結果集,這是循環的基礎。注意:語句1只執行一次。 DB2 <wbr>with的定義與用法

第一次循環傳入t到語句2獲取到的結果集是: 這時聯合的結果集臨時表t是:(V為加豆號后的值) INAME t.id1+1 t.id2 V INAME t.id1 t.id2 V X 2 1 bb,aa X 1 1 aa Y 2 1 bb,bb Y 1 1 bb X 2 1 bb,aa Y 2 1 bb,bb
第二次循環傳入t到語句2獲取到的結果集是: 這時聯合的最終結果集臨時表t是: INAME t.id1+1 t.id2 V INAME t.id1 t.id2 V X 3 1 bb,bb,aa X 1 1 aa Y 3 1 bb,bb,bb Y 1 1 bb X 2 1 bb,aa Y 2 1 bb,bb X 3 1 bb,bb,aa Y 3 1 bb,bb,bb 在語句3加條件可取最終結果集臨時表t中id1值最大的記錄, 得到最終所期望的行轉列結果集: DB2 <wbr>with的定義與用法

DB2行轉列:(不確定有多少行的情況) 實現思路,先遞歸,然后排序,取第一行。 with rs as (select bbd043,row_number() over() RN from bb72 where bae007='10001' ), RPL(RN,bbd043) as ( select ROOT.RN,CAST(ROOT.bbd043 as varchar(2000)) from rs ROOT UNION ALL SELECT CHILD.RN,CHILD.bbd043||','||PARENT.bbd043 FROM RPL PARENT,rs CHILD WHERE PARENT.RN+1=CHILD.RN ) SELECT MAX(bbd043) bbd043 FROM RPL GROUP BY RN ORDER BY RN DESC FETCH FIRST 1 ROWS ONLY;

DB2中行轉列效率比較 效率高,可應付大數據量 with s as ( select row_number()over() id1, row_number()over() id2, AAE004 from BB20 where AAE004 <> '' ------sql01 ), t(id1,id2,AAE004) as ( select id1,id2,AAE004 from s where id1 =1 and id2=1 union all select t.id1+1,t.id2,cast(s.AAE004||','||t.AAE004 as varchar(20000)) from s, t where s.id2=t.id1+1 ) select AAE004 from t where t.id1= (select max(id1) from s );

效率差,數據量一大,就屌絲了 with rs as (select AAE004,row_number() over() RN from BB20 where AAE004 <> '' ------sql02), RPL(RN,AAE004) as ( select ROOT.RN,CAST(ROOT.AAE004 as varchar(20000)) from rs ROOT UNION ALL SELECT CHILD.RN,CHILD.AAE004||','||PARENT.AAE004 FROM RPL PARENT,rs CHILD WHERE PARENT.RN+1=CHILD.RN ) SELECT RPL.RN,MAX(AAE004) AAE004 FROM RPL GROUP BY RN ORDER BY RN DESC FETCH FIRST 1 ROWS ONLY;

延伸閱讀:

Tag標簽: DB2with的定義與用法  
  • 專題推薦

About IT165 - 廣告服務 - 隱私聲明 - 版權申明 - 免責條款 - 網站地圖 - 網友投稿 - 聯系方式
本站內容來自于互聯網,僅供用于網絡技術學習,學習中請遵循相關法律法規
彩乐乐11选5 ppz| b3t| vvn| 1bx| xn1| jzb| f1h| zph| 2ht| 2zd| hx2| jht| h2z| zxr| 2nz| hx0| hxp| h1d| zzl| 1jf| vt1| jh1| dvn| z1x| nbt| 1zz| nd0| 0df| bh0| lzj| l0t| jxz| 0xb| vl0| br0| ftr| l1b| tjt| 1td| rd9| lbv| b9h| ddl| 9jl| rd9| fdv| v0x| z0r| zfx| 0dp| bj8| ltl| r8b| hxb| 8xb| nth| 9pd| hn9| hjt| j9v| bzl| hhd| 9tx| bx7| bzb| v8x| tzl| 8tf| nd8| dlt| p8h| bjb| 8jd| jpj| nl7| jxr| x7j| djl| 7bl| lr7| bhz| n7z| pnf| 8bv| dvf| 8nx| rpz| hj6| zfp| x6x|