Oracle 多行合并一行 方法

is null connect by prior no=pid; 你们自己看结果吧。 Text

深圳,武汉,上海,北京,天津,新加坡 ……
呵呵呵,虽然是做出来来,但是就像上面说讲的,这里只是另类的喜悦,因为这个不是我以前看到的那个解决方案,不过是通过这个方法,有用到了强大的connect
by已经分析函数over,仅是窃喜,
找寻工作还要继续,什么时候才很然给我拨开云雾找到你哟。

NOTE:特别声明一下内容转自网络 
 

练习: 通过子节点获得顶节点 

以前看过有人转换过的,当时仅仅惊叹了一下,就过去了,没有记下来,直至于用到的时候呢,开始到处找,找来找去都没有找不到痕迹了,心里也就郁郁寡欢呀。
今天无意间,看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 是取前记录, 和lead相对,
如果是简单的拼的话,树结构不就是,上一条记录就是下一条记录的父节点了么
这样我们用rownum,不就…. OK了 action select t.name, no, lag 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 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||name就可以看出端倪了
最后一变,拼成串 select sys_connect_by_path text from (select t.name,
no, lag pid from (select temp.*, rownum no from temp)) t start with pid

Sql代码 

从Root往树末梢递归 
select * from TBL_TEST 
start with id=1 
connect by prior id = pid 

Sql代码 

DEPTID PAREDEPTID NAME 
NUMBER NUMBER CHAR (40 Byte) 
部门id 父部门id(所属部门id) 部门名称 

这是一个比较典型的行列转换,有好几种实现方法 

Sql代码 
select * from persons.dept start with paredeptid=0 connect by prior
deptid=paredeptid 
Sql代码 
select * from persons.dept start with paredeptid=0 connect by prior
deptid=paredeptid 

2.使用sys_connect_by_path 

start with 条件1
connect by 条件2
where 条件3;

例:
select * from table
start with org_id = ‘HBHqfWGWPy’
connect by prior org_id = parent_id;

  1. create or replace function my_concat(n number)  
  2. return varchar2  
  3. is  
  4.  type typ_cursor is ref cursor;  
  5.  v_cursor typ_cursor;  
  6.  v_temp varchar2(10);  
  7.  v_result varchar2(4000):= ”;  
  8.  v_sql varchar2(200);  
  9. begin  
  10.  v_sql := ‘select a from t where i=’ || n ||’ order by d’;  
  11.  open v_cursor for v_sql;  
  12.  loop  
  13.     fetch v_cursor into v_temp;  
  14.     exit when v_cursor%notfound;  
  15.     v_result := v_result ||’,’ || v_temp;  
  16.  end loop;  
  17.  return substr(v_result,2);  
  18. end;  
  19.   
  20. SQL> select i,my_concat(i) from t group by i;  
  21.   
  22.          I MY_CONCAT(I)  
  23. ———- ——————–  
  24.          1 d,b,a  
  25.          2 z,t  

递归的种子也就是递归开始的地方 connect by 后面的”prior”
如果缺省:则只能查询到符合条件的起始行,并不进行递归查询; 

1.自定义函数实现 

select a.*,level from persons.dept a start with paredeptid=0 connect by
prior deptid=paredeptid 

  1. SQL> select * from t;  
  2.   
  3.          I A          D  
  4. ———- ———- ——————-  
  5.          1 b          2008-03-27 10:55:42  
  6.          1 a          2008-03-27 10:55:46  
  7.          1 d          2008-03-27 10:55:30  
  8.          2 z          2008-03-27 10:55:55  
  9.          2 t          2008-03-27 10:55:59  
  10.   
  11. — 要获得如下结果,注意字符串需要按照D列的时间排序:  
  12.   
  13. 1  d,b,a  
  14. 2  z,t  

select * from persons.dept start with deptid=76 connect by prior
paredeptid=deptid 

  1. SQL> select i,wmsys.wm_concat(a) from t group by i;  
  2.   
  3.          I WMSYS.WM_CONCAT(A)  
  4. ———- ——————–  
  5.          1 b,a,d  
  6.          2 z,t  
  7.   
  8. SQL> select i,wmsys.wm_concat(a)  
  9.   2  from  
  10.   3  (select * from t order by i,d)  
  11.   4  group by i;  
  12.   
  13.          I WMSYS.WM_CONCAT(A)  
  14. ———- ——————–  
  15.          1 d,b,a  
  16.          2 z,t  

connect by prior 后面所放的字段是有关系的,它指明了查询的方向。 

Sql代码 

        
简单说来是将一个树状结构存储在一张表里,比如一个表中存在两个字段:
org_id,parent_id那么通过表示每一条记录的parent是谁,就可以形成一个树状结构。

虽然这种方式可以实现需求,但是如果表t的数据量很大,i的值又很多的情况下,因为针对每个i值都要执行一句select,扫描和排序的次数和i的值成正比,性能会非常差。 

        简单介绍如下:
       
在扫描树结构表时,需要依此访问树结构的每个节点,一个节点只能访问一次,其访问的步骤如下:
        第一步:从根节点开始;
        第二步:访问该节点;
       
第三步:判断该节点有无未被访问的子节点,若有,则转向它最左侧的未被访问的子节,并执行第二步,否则执行第四步;
        第四步:若该节点为根节点,则访问完毕,否则执行第五步;
        第五步:返回到该节点的父节点,并执行第三步骤。
        总之:扫描整个树结构的过程也即是中序遍历树的过程。

从执行计划上来看,这种方式只需要扫描两次表,比自定义函数的方法,效率要高很多,尤其是表中数据量较大的时候: 
图片 1 
3.使用wm_sys.wm_concat 
这个函数也可以实现类似的行列转换需求,但是似乎没有办法做到直接根据另外一列排序,所以需要先通过子查询或者临时表排好序: 

# re:
Oracle中start with…connect by子句的用法 2010-03-05 18:04 | xzc

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*
*
Website