oracle列轉(zhuǎn)行sql詳解-展示頁
2025-07-05 11:54本頁面
【正文】 FORDSMITHKINGBLAKEKINGBLAKEALLENKINGBLAKEWARDKINGBLAKEMARTINKINGBLAKETURNERKINGBLAKEJAMESKINGCLARKKINGCLARKMILLER其實(shí)SYS_CONNECT_BY_PATH這個(gè)函數(shù)是oracle9i才新提出來的！它一定要和connect by子句合用！第一個(gè)參數(shù)是形成樹形式的字段，第二個(gè)參數(shù)是父級和其子級分隔顯示用的分隔符！START WITH 代表你要開始遍歷的的節(jié)點(diǎn)，CONNECT BY PRIOR 是標(biāo)示父子關(guān)系的對應(yīng)！如下例子：select max(substr(sys_connect_by_path(column_name,39。KING39。39。 CONNECT BY PRIOR empno = mgr。 CONNECT BY PRIOR = SYS_CONNECT_BY_PATH 學(xué)習(xí)20080908 10:59SELECT enameFROM START WITH ename = 39。), 2) as allname ,connect_by_root as root, 是單一操作符，返回當(dāng)前層的最頂層節(jié)點(diǎn)connect_by_isleaf as IsLeaf, 是偽列，判斷當(dāng)前層是否為葉子節(jié)點(diǎn)，1代表是，0代表否level as lel 是偽列，顯示當(dāng)前節(jié)點(diǎn)層所處的層數(shù)FROM dk_project_area_info tSTART WITH = 39。) activeDate FROM (SELECT DK_ID, activeDate, DK_ID || rn rchild, DK_ID || (rn 1) rfather FROM (SELECT ,查詢項(xiàng)目所在地樹形結(jié)構(gòu)全名SELECT ,substr(sys_connect_by_path(, 39。當(dāng)期時(shí)間 貸款時(shí)間SELECT DK_ID, max(substr(activeDate, 2)) activeDate FROM (SELECT DK_ID, sys_connect_by_path(activeDate, 39。,39。39。項(xiàng)目所在地39。KING39。 得到結(jié)果為：KINGJONESSCOTTADAMSFORDSMITHBLAKEALLENWARDMARTINTURNERJAMES而：SELECT SYS_CONNECT_BY_PATH(ename, 39。) Path FROM START WITH ename = 39。 CONNECT BY PRIOR empno = mgr。,39。AA_TEST39。是將列用,進(jìn)行分割成為一行，然后將首個(gè)，去掉，只取取最大的那個(gè)數(shù)據(jù)。Table created.SQL insert into dept values(1,39。,null)。浙江分公司39。1 row created.SQL insert into dept values(3,39。,2)。Commit plete.SQL select max(substr(sys_connect_by_path(deptname,39。),2)) from dept connect by prior deptno=mgrno。,39。,39。DEPT39。MAX(SUBSTR(SYS_CONNECT_BY_PATH(COLUMN_NAME,39。),2))
規(guī)章制度相關(guān)推薦
鄂ICP備17016276號(hào)-1