你的位置:首页 > 数据库

[数据库]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;