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;
最新文章
- python——线程与多线程进阶
- java容器(java编程思想第四版-读书笔记)
- java开发_读写txt文件操作
- Android Studio中的Module,Facet
- VC远控(一)界面设计及套接字连接测试
- JAVA反射原理
- empty()和remove()的区别
- 为你的WordPress博客添加CSS3炫酷读者墙
- Linux系统根目录各文件夹的含义
- React中使用echarts
- Sql 根据当前时间,获取星期一具体日期
- oracle数据库访问形式
- EL11个内置对象
- class特性
- 20155321 《网络攻防》 Exp4 恶意代码分析
- python IDE 集合
- Sql Server-查询一列的数据进行拼接
- long polling
- 【转载】OGRE 2.1 Windows 编译
- Hadoop3.0新特性