oracle sys_connect_by_path 函数 结果集连接

以前看过有人转换过的，当时仅仅惊叹了一下，就过去了，没有记下来，直至于用到的时候呢，开始到处找，找来找去都没有找不到痕迹了，心里也就郁郁寡欢呀。
今天无意间，看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，仅是窃喜，
找寻工作还要继续，什么时候才很然给我拨开云雾找到你哟。

赞 (0)

相关推荐

  • window中oracle环境变量设置方法分享

    window server中Oracle的环境变量设置 1.右击"我的电脑"->选择"属性"->选择"高级"->单击"环境变量"2.选择"Path"这一行,单击"编辑",在"Path"的变量值文本框的最后面先加入一个分号":",然后再分号后面加入sqlplus文件的目录路径,如"C:\Program Files\orac

  • 安装Oracle时出现环境变量Path的值大于1023的解决办法

    安装Oracle时出现环境变量Path的值大于1023的解决办法

    出现的情况我就不说了,直接重点: 计算机->属性->高级系统设置->高级->环境变量 1)在"系统变量"编辑Path,全选将其中的路径全部复制出来放到文本文档中: 2)新建一个系统变量取名Path1 ,剪切Path中的所有变量放入Path1然后保存: 3)并将Path中全选删除改为默认值:%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;: 4)最后在Path中建立一个引用,也就是引用Pa

  • oracle sys_connect_by_path 函数 结果集连接

    以前看过有人转换过的,当时仅仅惊叹了一下,就过去了,没有记下来,直至于用到的时候呢,开始到处找,找来找去都没有找不到痕迹了,心里也就郁郁寡欢呀. 今天无意间,看connect by的使用,看到了sys_connect_by_path的用法,算是给我一个另类的惊喜了,sys_connect_by_path(columnname, seperator) 也可以拼出串来,不过这个函数本身不是用来给我们做这个结果集连接用的,而是用来构造树路径的,所以需要和connect by一起来用. 呵呵呵,在这里嚣

  • Oracle 多行记录合并/连接/聚合字符串的几种方法

    什么是合并多行字符串(连接字符串)呢,例如: SQL> desc test; Name Type Nullable Default Comments ------- ------------ -------- ------- -------- COUNTRY VARCHAR2(20) Y CITY VARCHAR2(20) Y SQL> select * from test; COUNTRY CITY -------------------- -------------------- 中国 台

  • PHP实现的oracle分页函数实例

    本文实例讲述了PHP实现的oracle分页函数.分享给大家供大家参考,具体如下: mysql有limit,分页的时候,我们可以用limit 30,40,而oracle没有limit,用其他的方法来代替--rownum. 一.底层方法 /** * 数据连接 */ function connect($db_user, $db_pwd, $db_name, $db_nls) { $this->dbh = oci_connect($db_user, $db_pwd, $db_name, $db_nls)

  • 基于PHP中自带的字符串操作函数合集

    1.查找字符位置函数: strpos($str,search,[int])://查找search在$str中的第一次位置从int开始: strrpos($str,search,[int])://查找search在$str中的最后一次出现的位置从int开始 2.提取子字符函数(双字节) submit($str,int start[,int length])://从$str中strat位置开始提取[length长度的字符串]. strstr($str1,$str2)://从$str1(第一个的位置)

  • 关于C#中使用Oracle存储过程返回结果集的问题

    问题: 在MSSQLServer中定义的存储过程可以直接返回一个数据集,如: create procedure sp_getAllEmployees as SELECT * FROM [NORTHWND].[dbo].[Employees] 在Oracle数据库中这样定义是错误的,怎么解决? 办法: Oracle中可以使用游标(Cursor)对数据集进行操作,但在存储过程输出参数中直接使用Cursor错误,此时的Cursor应该是一个定义游标的关键字并非类型,所以先要定义一个包,在包中声明全局的

  • Oracle DECODE函数语法使用介绍

    Oracle DECODE函数功能很强,下面就为您详细介绍Oracle DECODE函数的用法,希望可以让您对Oracle DECODE函数有更多的了解. Oracle DECODE函数 Oracle DECODE函数是Oracle公司独家提供的功能,它是一个功能很强的函数.它虽然不是SQL的标准,但对于性能非常有用.到目前,其他的数据库供应商还不能提供类似DECODE的功能,甚至有的数据库的供应商批评Oracle的SQL不标准.实际上,这种批评有些片面或不够水平.就象有些马车制造商抱怨亨利.福

  • Oracle日期函数简介

    Oracle日期函数用于对Oracle数据库中的日期及时间进行处理,下面就为您详细介绍Oracle日期函数的用法,希望对您能有所启迪. (1)SYSDATE和TRUNC 两个Oracle日期函数取Oracle服务器系统的日期和截掉小数部分的功能.观察以下操作: create table test_date (name varchar2(20), p_date date); insert into test_date values('name1',sysdate); select * from t

  • Oracle to_date()函数的用法介绍

    在Oracle数据库中,Oracle to_date()函数是我们经常使用的函数,下面就为您详细介绍Oracle to_date()函数的用法,希望可以对您有所启迪. to_date()与24小时制表示法及mm分钟的显示: 一.在使用Oracle的to_date函数来做日期转换时,很多Java程序员也许会直接的采用"yyyy-MM-dd HH:mm:ss"的格式作为格式进行转换,但是在Oracle中会引起错误:"ORA 01810 格式代码出现两次". select

  • oracle trunc()函数的使用方法

    oracle trunc()函数是最常用的函数之一,下面就为您介绍oracle trunc()函数的用法,供您参考,希望可以让您对oracle trunc()函数有更深的认识. 1.TRUNC(for dates) TRUNC函数为指定元素而截去的日期值. 其具体的语法格式如下: TRUNC(date[,fmt]) 其中: date一个日期值 fmt        日期格式,该日期将由指定的元素格式所截去.忽略它则由最近的日期截去 下面是该函数的使用情况: TRUNC(TO_DATE('24-N

  • Oracle文本函数简介

    Oracle文本函数使我们常用的函数,下面就为您介绍几种Oracle文本函数的用法,供您参考学习,希望可以让您对Oracle文本函数有更深的认识. (1)UPPER.LOWER和INITCAP 这三个函数更改提供给它们的文体的大小写. select upper(product_name) from product; select lower(product_name) from product; select initcap(product_name) from product; 函数INITC

随机推荐