Oracle層次查詢，Oracle層次

Oracle層次查詢，Oracle層次

Oracle層次查詢的語法如下：

下面根據兩道“燒腦”的題具體來體現：

1. 根據時間先後順序，十二星座的英文名稱用逗號串起來為'Aries,Taurus,Gemini,Cancer,Leo,Virgo,Libra,Scorpio,Sagittarius,Capricorn,Aquarius,Pisces'，請用帶層次查詢的sql替換下面的sql中的[...]部分，使該sql能將字符串拆分為12條記錄。

with t as (select 'Aries,Taurus,Gemini,Cancer,Leo,Virgo,Libra,Scorpio,Sagittarius,Capricorn,Aquarius,Pisces' str from dual)
[...]

其實，該題有幾種不同的解法。

解法1：利用replace函數

with t as (select 'Aries,Taurus,Gemini,Cancer,Leo,Virgo,Libra,Scorpio,Sagittarius,Capricorn,Aquarius,Pisces' str from dual)
select replace(str,',',chr(10)) constellation from t

但是這種解法有點瑕疵，題目要求輸出12條記錄，該解法雖然呈現的是12行，但實際只是一行記錄。

解法2：利用層次查詢

with t as (select 'Aries,Taurus,Gemini,Cancer,Leo,Virgo,Libra,Scorpio,Sagittarius,Capricorn,Aquarius,Pisces' str from dual)
select regexp_substr(str,'\w{1,}',1,rownum) constellation from t,dual connect by rownum<=12

這裡同時也用到了正則表達式

解法3：非層次查詢

with t as (select 'Aries,Taurus,Gemini,Cancer,Leo,Virgo,Libra,Scorpio,Sagittarius,Capricorn,Aquarius,Pisces' str from dual),
t1 as (select instr(str||',',',',1,rownum)pos from t,dual connect by rownum<=12),
t2 as (select pos,lag(pos,1,0)over(order by pos) prev from t1)
select substr(str,prev+1,pos-prev-1) constellation  from t,t2

這種解法花費了較多時間才想出。

2. 已知在11g下，下面sql

select deptno, cast(listagg(ename,',')within group(order by empno) as varchar2(50)) nl from emp group by deptno order by deptno;
的運行結果為：

DEPTNO NL
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,JONES,SCOTT,ADAMS,FORD
        30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES

請用層次查詢寫出在10g下可以達到得到同樣結果的sql

with t as (select deptno,ename,lag(ename)over(partition by deptno order by ename)lag_name from emp),
t1 as (select deptno,max(sys_connect_by_path(ename,',')) name from t start with lag_name is null connect by prior ename=lag_name group by deptno)
select deptno,cast(regexp_replace(name,',','',1,1) as varchar2(40))nl from t1 order by 1;