以前看过有人转换过的,当时仅仅惊叹了一下,就过去了,没有记下来,直至于用到的时候呢,开始到处找,找来找去都没有找不到痕迹了,心里也就郁郁寡欢呀。
今天无意间,看connect by的使用,看到了sys_connect_by_path的用法,算是给我一个另类的惊喜了,sys_connect_by_path(columnname, seperator) 也可以拼出串来,不过这个函数本身不是用来给我们做这个结果集连接用的,而是用来构造树路径的,所以需要和connect by一起来用。
呵呵呵,在这里嚣张了一把,基于对oracle的一些函数的了解的基础上,看我是怎样硬生生的把一个没有树结构的普通表或者结果集做出我们想要的东西来。
magic is start.
道具,一个普通表,就一个字段 name, 姑且叫表名为test_sysconnectbypath吧,表名太长,嘻嘻,不怕,别名之。
以下为该表数据
NAME
------------------
深圳
武汉
上海
北京
天津
新加坡
别名之
SQL>with temp as (select name form test_sysconnectbypath);
这是别名的写法,我们下面的sql语句就可以用temp来代替这个结果集。当然这个()里面可以是你自己的复杂查询出来的结果集也行
第一变性开始,把这个变成有树形结构的
怎么才能变形成树结构了,大家马上想到,加一个pid,和id才行哟,这里没有,我们就给他们加上吧。不过,加了id,怎么来填他们的结构数据呢,这里需要另一个函数显圣了 lag() , lag() 是取前记录, 和lead相对, 如果是简单的拼的话,树结构不就是,上一条记录就是下一条记录的父节点了么
这样我们用rownum,不就.... OK了
action
select t.name, no, lag(no) over(order by no) pid from (select temp.*, rownum no from temp) t;
结果出来了
NAME NO PID
-------------------- ---------- ----------
深圳 1
武汉 2 1
上海 3 2
北京 4 3
天津 5 4
新加坡 6 5
现在就是个树形了吧。
再变树
action
select * from (select t.name, no, lag(no) over(order by no) pid from (select temp.*, rownum no from temp)) t start with pid is null connect by prior no=pid;
看看结果吧
结果出来了
NAME NO PID
-------------------- ---------- ----------
深圳 1
武汉 2 1
上海 3 2
北京 4 3
天津 5 4
新加坡 6 5
奇怪结果没有变哟,是的,这里只是把树给选出来了,你如果加个lpad(' ', 4*level, '*')||name就可以看出端倪了
最后一变,拼成串
select sys_connect_by_path(name. ',') text from (select t.name, no, lag(no) over(order by no) pid from (select temp.*, rownum no from temp)) t start with pid is null connect by prior no=pid;
你们自己看结果吧。
Text
--------------------------------------------------
深圳,武汉,上海,北京,天津,新加坡
......
呵呵呵,虽然是做出来来,但是就像上面说讲的,这里只是另类的喜悦,因为这个不是我以前看到的那个解决方案,不过是通过这个方法,有用到了强大的connect by已经分析函数over,仅是窃喜,
找寻工作还要继续,什么时候才很然给我拨开云雾找到你哟。
oracle sys_connect_by_path 函数 结果集连接
2019-11-04 16:20oracle教程网 Oracle
这几天和几个网上朋友一起探讨oracle开发中的一些特别之处,谈到了竖横对换的方式。
延伸 · 阅读
- 2022-03-07Oracle Linux 能否成为企业级 CentOS 的替代品?
- 2022-03-05Oracle VM VirtualBox 虚拟机硬盘扩容
- 2022-02-28oracle删除超过N天数据脚本的方法
- 2022-02-25oracle重置序列从0开始递增1
- 2022-02-24Oracle 触发器trigger使用案例
- 2022-02-23Linux 7.4上安装配置Oracle 11.2.0.4图文教程
- Oracle
oracle 动态AdvStringGrid完美示例 (AdvStringGrid使用技巧/Cells)
本方法实现用常量和常量数组完美创建和控制动态TAdvStringGrid。 ...
- Oracle
oracle使用instr或like方法判断是否包含字符串
使用contains谓词有个条件,那就是列要建立索引,本节主要介绍了oracle使用instr或like方法判断是否包含字符串,需要的朋友可以参考下 ...
- Oracle
Oracle数据库由dataguard备库引起的log file sync等待问题
这篇文章主要介绍了Oracle数据库由dataguard备库引起的log file sync等待,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋...
- Oracle
[Oracle] dbms_metadata.get_ddl 的使用方法总结
以下是对Oracle中dbms_metadata.get_ddl的用法进行了详细的分析介绍,需要的朋友参考下 ...
- Oracle
oracle中利用关键字rownum查询前20名员工信息及rownum用法
这篇文章主要介绍了oracle中利用关键字rownum查询前20名员工信息,本文通过代码给大家简单介绍了ORACLE 中ROWNUM用法,非常不错,具有一定的参考借鉴价值,需...
- Oracle
Linux中Oracle数据库备份
在Linux中Oracle数据库备份的方法有很多,就像mysql一样可以使用不同方法进行备份oracle数据库 ...
- Oracle
oracle 优化的一点体会
oracle 优化的一点体会大家可以参考下,提升运行效率。 ...
- Oracle
Oracle查看表结构命令详解
这篇文章主要介绍了Oracle查看表结构命令详解的相关资料,需要的朋友可以参考下 ...