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;