注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

Alex的博客

 
 
 

日志

 
 

Oracle列转行 sys_connect_by_path  

2010-04-16 09:58:38|  分类: 编程 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
最近在论坛上,经常会看到关于分组后字段拼接的问题,        
      大概是类似下列的情形:        
      select       no,q       from       test        
           
      NO                                       Q        
      ----------       ------------------------------        
      001                                   n1        
      001                                   n2        
      001                                   n3        
      001                                   n4        
      001                                   n5        
      002                                   m1        
      003                                   t1        
      003                                   t2        
      003                                   t3        
      003                                   t4        
      003                                   t5        
      003                                   t6        
           
      12       rows       selected        
           
      最后要得到类似于如下的结果:        
      001                                   n1;n2;n3;n4;n5        
      002                                   m1        
      003                                   t1;t2;t3;t4;t5;t6        
           
              通常大家都认为这类问题无法用一句SQL解决,本来我也这么认为,可是今天无意中突然有了灵感,原来是可以这么做的:        
              前几天有人提到过sys_connect_by_path的用法,我想这里是不是也能用到这个方法,如果能做到的话,不用函数或存贮过程也可以做到了;要用到sys_connect_by_path,首先要自己构建树型的结构,并且树的每个分支都是单根的,例如1-〉2-〉3-〉4,不会存在1-〉2,1-〉3的情况;        
              我是这么构建树,很简单的,看下面的结果就会知道了:        
      SQL>  select       no,q,rn,lead(rn)       over(partition       by       no     order     by     rn)     rn1        
                from       (select       no,q,row_number()       over(order     by     no,q     desc)    rn    from   test)        
 
           
      NO                                       Q                                                                                                                                                           RN                                   RN1        
      ----------       ------------------------------       ----------       ----------        
      001                                   n5                                                                                                                                                           1                                           2        
      001                                   n4                                                                                                                                                           2                                           3        
      001                                   n3                                                                                                                                                           3                                           4        
      001                                   n2                                                                                                                                                           4                                           5        
      001                                   n1                                                                                                                                                           5            
      002                                   m1                                                                                                                                                           6            
      003                                   t6                                                                                                                                                           7                                           8        
      003                                   t5                                                                                                                                                           8                                           9        
      003                                   t4                                                                                                                                                           9                                       10        
      003                                   t3                                                                                                                                                       10                                       11        
      003                                   t2                                                                                                                                                       11                                       12        
      003                                   t1                                                                                                                                                       12            
           
      12       rows       selected        
           
      有了这个树型的结构,接下来的事就好办了,只要取出拥有全路径的那个path,问题就解决了,先看no=‘001’的分组:        
      select       no,sys_connect_by_path(q,';')       result       from            
                                  (select       no,q,rn,lead(rn)       over(partition       by       no       order       by       rn)       rn1            
                                  from       (select       no,q,row_number()       over(order       by       no,q       desc)       rn       from       test)        
                                  )        
      start       with       no       =       '001'       and       rn1       is       null       connect       by       rn1       =       prior       rn        
      SQL>            
              6           /        
           
      NO                                       RESULT        
      ----------       --------------------------------------------------------------------------------        
      001                                   ;n1        
      001                                   ;n1;n2        
      001                                   ;n1;n2;n3        
      001                                   ;n1;n2;n3;n4        
      001                                   ;n1;n2;n3;n4;n5        
           
      上面结果的最后1条就是我们要得结果了        
      要得到每组的结果,可以下面这样        
           
      select       t.*,        
                      (        
                                      select       max(sys_connect_by_path(q,';'))       result       from            
                                                                  (select       no,q,rn,lead(rn)       over(partition       by       no       order       by       rn)       rn1            
                                                                  from       (select       no,q,row_number()       over(order       by       no,q       desc)       rn       from       test)        
                                                                  )        
                                      start       with       no       =       t.no       and       rn1       is       null       connect       by       rn1       =       prior       rn        
                                  )       value        
      from       (select       distinct       no       from       test)           t        
           
      SQL>            
          10           /        
           
      NO                                       VALUE        
      ----------       --------------------------------------------------------------------------------        
      001                                   ;n1;n2;n3;n4;n5        
      002                                   ;m1        
      003                                   ;t1;t2;t3;t4;t5;t6        
           
      对上面结果稍加处理就可以了,希望对大家有帮助:)    
  评论这张
 
阅读(646)| 评论(0)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018