oracle 列轉(zhuǎn)行sql詳解.doc

-當(dāng)期時間 貸款時間SELECT DK_ID, max(substr(activeDate, 2) activeDate FROM (SELECT DK_ID, sys_connect_by_path(activeDate, ,) activeDate FROM (SELECT DK_ID, activeDate, DK_ID | rn rchild, DK_ID | (rn - 1) rfather FROM (SELECT TEMP.DK_ID,-查詢項目所在地樹形結(jié)構(gòu)全名SELECT t.area_id,substr(sys_connect_by_path(t.area_name, -), 2) as allname ,connect_by_root t.area_name as root, -是單一操作符，返回當(dāng)前層的最頂層節(jié)點connect_by_isleaf as IsLeaf, -是偽列，判斷當(dāng)前層是否為葉子節(jié)點，1代表是，0代表否level as lel -是偽列，顯示當(dāng)前節(jié)點層所處的層數(shù)FROM dk_project_area_info tSTART WITH t.area_name = 項目所在地 CONNECT BY PRIOR t.area_id = t.area_pidSYS_CONNECT_BY_PATH 學(xué)習(xí)2008-09-08 10:59SELECT enameFROM scott.emp START WITH ename = KING CONNECT BY PRIOR empno = mgr; 得到結(jié)果為：KINGJONESSCOTTADAMSFORDSMITHBLAKEALLENWARDMARTINTURNERJAMES而：SELECT SYS_CONNECT_BY_PATH(ename, ) Path FROM scott.emp START WITH ename = KING CONNECT BY PRIOR empno = mgr;得到結(jié)果為：KINGKINGJONESKINGJONESSCOTTKINGJONESSCOTTADAMSKINGJONESFORDKINGJONESFORDSMITHKINGBLAKEKINGBLAKEALLENKINGBLAKEWARDKINGBLAKEMARTINKINGBLAKETURNERKINGBLAKEJAMESKINGCLARKKINGCLARKMILLER其實SYS_CONNECT_BY_PATH這個函數(shù)是oracle9i才新提出來的！它一定要和connect by子句合用！第一個參數(shù)是形成樹形式的字段，第二個參數(shù)是父級和其子級分隔顯示用的分隔符！START WITH 代表你要開始遍歷的的節(jié)點，CONNECT BY PRIOR 是標(biāo)示父子關(guān)系的對應(yīng)！如下例子：select max(substr(sys_connect_by_path(column_name,),2)from (select column_name,rownum rn from user_tab_columns where table_name =AA_TEST)start with rn=1 connect by rn=rownum ;是將列用,進行分割成為一行，然后將首個，去掉，只取取最大的那個數(shù)據(jù)。下面是別人的例子：1、帶層次關(guān)系SQL create table dept(deptno number,deptname varchar2(20),mgrno number);Table created.SQL insert into dept values(1,總公司,null);1 row created.SQL insert into dept values(2,浙江分公司,1);1 row created.SQL insert into dept values(3,杭州分公司,2);1 row created.SQL commit;Commit complete.SQL select max(substr(sys_connect_by_path(deptname,),2) from dept connect by prior deptno=mgrno;MAX(SUBSTR(SYS_CONNECT_BY_PATH(DEPTNAME,),2)-總公司,浙江分公司,杭州分公司2、行列轉(zhuǎn)換如把一個表的所有列連成一行，用逗號分隔:SQL select max(substr(sys_connect_by_path(column_name,),2)from (select column_name,rownum rn from user_tab_columns where table_name =DEPT)start with rn=1 connect by rn=rownum ;MAX(SUBSTR(SYS_CONNECT_BY_PATH(COLUMN_NAME,),2)-DEPTNO,DEPTNAME,MGRNO connect by 例子2009-04-21 09:18層次查詢子句connect by，用于構(gòu)造層次結(jié)果集的查詢。 語法： START WITH condition CONNECT BY NOCYCLE condition 說明： a、START WITH：告訴系統(tǒng)以哪個節(jié)點作為根結(jié)點開始查找并構(gòu)造結(jié)果集，該節(jié)點即為返回記錄中的最高節(jié)點。 b、當(dāng)分層查詢中存在上下層互為父子節(jié)點的情況時，會返回ORA-01436錯誤。此時，需要在connect by后面加上NOCYCLE關(guān)鍵字。同時，可用connect_by_iscycle偽列定位出存在互為父子循環(huán)的具體節(jié)點。connect_by_iscycle必須要跟關(guān)鍵字NOCYCLE結(jié)合起來使用，用法見示例2。 用法舉例： 示例1：顯示所有地名關(guān)系結(jié)構(gòu)。 SQL select * from t; AREA_ID AREA_NAME MGR_ID - - - 86 中國 01 北京 86 02 福建 86 0101 海淀區(qū) 01 0102 朝陽區(qū) 01 0103 東城區(qū) 01 0104 西城區(qū) 01 0201 廈門 02 0202 福州 02 020101 湖里 0201 020102 思明 0201 010401 復(fù)興門 0104 010402 西單 0104 已選擇13行。 SQL SQL set pagesize 50 SQL col AreaName for a12 SQL col Root for a10 SQL col Path for a24 SQL SQL select rpad( , 2*(level-1), ) | area_name AreaName, 2 connect_by_root area_name Root, 3 connect_by_isleaf IsLeaf, 4 level , 5 SYS_CONNECT_BY_PATH(area_name, /) Path 6 from t 7 start with mgr_id is null 8 connect by prior area_id = mgr_id; AreaName Root IsLeaf LEVEL Path - - - - - 中國 中國 0 1 /中國 北京 中國 0 2 /中國/北京 海淀區(qū) 中國 1 3 /中國/北京/海淀區(qū) 朝陽區(qū) 中國 1 3 /中國/北京/朝陽區(qū) 東城區(qū) 中國 1 3 /中國/北京/東城區(qū) 西城區(qū) 中國 0 3 /中國/北京/西城區(qū) 復(fù)興門 中國 1 4 /中國/北京/西城區(qū)/復(fù)興門 西單 中國 1 4 /中國/北京/西城區(qū)/西單 福建 中國 0 2 /中國/福建 廈門 中國 0 3 /中國/福建/廈門 湖里 中國 1 4 /中國/福建/廈門/湖里 思明 中國 1 4 /中國/福建/廈門/思明 福州 中國 1 3 /中國/福建/福州 已選擇13行。 說明： a、prior：是單一操作符，放在列名的前面，等號左右均可； 放在父 ID 就是 尋找 祖先節(jié)點 ，放到本身 ID就是尋找 子節(jié)點 ；b、connect_by_root：是單一操作符，返回當(dāng)前層的最頂層節(jié)點； c、connect_by_isleaf：是偽列，判斷當(dāng)前層是否為葉子節(jié)點，1代表是，0代表否； d、level：是偽列，顯示當(dāng)前節(jié)點層所處的層數(shù)； e、SYS_CONNECT_BY_PATH：是函數(shù)，顯示當(dāng)前層的詳細(xì)路徑。 示例2：找出人事部門中存在跟其他部門互為管理者的人員名單。 SQL select * from t2; EMP DEPT MGR - - - 劉濤 總裁辦 李飛 總裁辦 劉濤 張強 總裁辦 劉濤 王鵬 人事 李飛 李華 人事 李飛 張強 人事 李飛 李飛 行政 張強 吳華 行政 張強 已選擇8行。 SQL SQL col emp for a12 SQL select rpad( , 2*(level-1), ) | emp emp 2 from t2 3 start with dept =人事 4 connect by prior emp = mgr; ERROR: ORA-01436: 用戶數(shù)據(jù)中的 CONNECT BY 循環(huán) 未選定行 說明：張強和李飛互為管理者，因此，要用nocycle，如下所示： SQL select rpad( , 2*(level-1), ) | emp emp 2 from t2 3 start with dept =人事 4 connect by nocycle prior emp = mgr; emp - 王鵬 李華 張強 李飛 王鵬 李華 吳華 已選擇7行。 SQL SQL select rpad( , 2*(level-1), ) | emp emp, 2 connect_by_iscycle IsCycle 3 from t2 4 start with dept =人事 5 connect by prior emp = mgr; connect by prior emp = mgr * 第 5 行出現(xiàn)錯誤: ORA-30930: CONNECT_BY_ISCYCLE 偽列要求 NOCYCLE 關(guān)鍵字 說明：在用connect_by_iscycle定位節(jié)點時，也要用nocycle關(guān)鍵字，如下所示： SQL SQL select rpad( , 2*(level-1), ) | emp emp, 2 connect_by_iscycle IsCycle 3 from t2 4 start with dept =人事 5 connect by nocycle prior emp = mgr;emp IsCycle - - 王鵬 0 李華 0 張強 0 李飛 1 王鵬 0 李華 0 吳華 0 已選擇7行。 SQL 示例3：僅顯示第二層（即level=2）省市名稱。 SQL select rpad( , 2*(level-1), ) | area_name AreaName 2 from t 3 where level = 2 4 start with mgr_id is null 5 connect by prior area_id = mgr_id; AreaName - 北京 福建 示例4：用connect by構(gòu)造序列。 SQL SQL select rownum rn 2 from dual 3 connect by rownum select rownum*2 -1 rn 2 from dual 3 connect by rownum=10; RN - 1 3 5 7 9 11 13 15 17 19 已選擇10行。 - 附：建表語句 create table t (area_id varchar2(6), area_name varchar2(10), mgr_id varchar2(6); insert into t values(86, 中國, null); insert into t values(01, 北京, 86); insert into t values(02, 福建, 86); insert into t values(0101, 海淀區(qū), 01); insert into t values(0102, 朝陽區(qū), 01); insert into t values(0103, 東城區(qū), 01); insert into t values(0104, 西城區(qū), 01); insert into t values(0201, 廈門, 02); insert into t values(0202, 福州, 02); insert into t values(020101, 湖里, 0201); insert into t values(020102, 思明, 0201); insert into t values(010401, 復(fù)興門, 0104); insert into t values(010402, 西單, 0104); commit; create table t2 (emp varchar2(10), dept varchar2(6), mgr varchar2(10);