查看: 21029|回复: 59

[精华] 原创+突发奇想+分享-----关于分组后字段拼接的问题

[复制链接]
论坛徽章:
0
发表于 2005-7-22 12:57 | 显示全部楼层 |阅读模式
最近在论坛上,经常会看到关于分组后字段拼接的问题,
大概是类似下列的情形:
SQL> select no,q from test
  2  /

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
  2  from (select no,q,row_number() over(order by no,q desc) rn from test)
  3  /

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条就是我们要得结果了
要得到每组的结果,可以下面这样
[PHP]
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
[/PHP]

SQL>
10  /

NO         VALUE
---------- --------------------------------------------------------------------------------
001        ;n1;n2;n3;n4;n5
002        ;m1
003        ;t1;t2;t3;t4;t5;t6

对上面结果稍加处理就可以了,希望对大家有帮助:)
论坛徽章:
0
 楼主| 发表于 2005-7-22 13:47 | 显示全部楼层
稍微改进下:
[PHP]
select no,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 rn1 is null
connect by rn1 = prior rn
group by no;



[/PHP]

使用道具 举报

回复
论坛徽章:
27
ITPUB元老
日期:2005-02-28 12:57:002012新春纪念徽章
日期:2012-02-13 15:11:182012新春纪念徽章
日期:2012-02-13 15:11:182012新春纪念徽章
日期:2012-02-13 15:11:182012新春纪念徽章
日期:2012-02-13 15:11:18马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:142012新春纪念徽章
日期:2012-02-13 15:11:18
发表于 2005-7-22 13:56 | 显示全部楼层
非常不错的思路。



谢谢分享。

使用道具 举报

回复
论坛徽章:
44
双鱼座
日期:2016-01-07 20:57:31奔驰
日期:2013-08-02 22:22:552013年新春福章
日期:2013-02-25 14:51:24迷宫蛋
日期:2013-01-29 22:12:11蛋疼蛋
日期:2013-01-07 15:50:53ITPUB十周年纪念徽章
日期:2011-11-01 16:20:28紫蛋头
日期:2011-07-31 11:27:01蜘蛛蛋
日期:2011-06-14 14:20:33蛋疼蛋
日期:2011-06-03 19:39:27SQL大赛参与纪念
日期:2011-04-13 12:08:17
发表于 2005-7-22 15:29 | 显示全部楼层
8错  

使用道具 举报

回复
招聘 : 数据库管理员
论坛徽章:
38
ITPUB北京九华山庄2008年会纪念徽章
日期:2008-01-21 16:50:24马上有对象
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有车
日期:2014-02-19 11:55:14现任管理团队成员
日期:2012-10-18 17:11:21版主4段
日期:2012-05-15 15:24:112012新春纪念徽章
日期:2012-02-13 15:09:232012新春纪念徽章
日期:2012-02-13 15:09:232012新春纪念徽章
日期:2012-02-13 15:09:23
发表于 2005-7-22 16:21 | 显示全部楼层
好思路,谢谢分享!我顶一下
只是不知道这三种方法:
你的方法、使用数据库函数、使用自定义聚合函数
哪一种的效率会最高,我猜想,或许自定义聚合函数的效率是最高的,因为其它两种方法都会产生一些递归的查询操作,但我没试验过。谁有兴趣做个实验?呵呵

使用道具 举报

回复
论坛徽章:
52
SQL数据库编程大师
日期:2011-04-13 12:09:01奥运会纪念徽章:水球
日期:2012-10-08 09:35:51蛋疼蛋
日期:2012-11-19 10:28:53鲜花蛋
日期:2012-12-05 14:37:57紫蛋头
日期:2012-12-11 17:27:27紫蛋头
日期:2012-12-24 23:00:26兰博基尼
日期:2013-11-21 09:19:58马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14
发表于 2005-7-22 17:54 | 显示全部楼层
8错8错

使用道具 举报

回复
论坛徽章:
0
发表于 2005-7-22 19:46 | 显示全部楼层
最初由 lwxxrq 发布
[B]好思路,谢谢分享!我顶一下
只是不知道这三种方法:
你的方法、使用数据库函数、使用自定义聚合函数
哪一种的效率会最高,我猜想,或许自定义聚合函数的效率是最高的,因为其它两种方法都会产生一些递归的查询操作,但我没试验过。谁有兴趣做个实验?呵呵 [/B]



呵呵,我对结果感兴趣……

使用道具 举报

回复
论坛徽章:
0
发表于 2005-7-22 23:29 | 显示全部楼层
不错,支持一下

使用道具 举报

回复
论坛徽章:
0
 楼主| 发表于 2005-8-1 11:30 | 显示全部楼层
改进下算法,少一层嵌套查询,效率会好些:
[PHP]
select no,max(sys_connect_by_path(q,';')) result from
(
       select no,q,(row_number() over(order by no,q desc) + rank() over(order by no)) rn
       from test
)
connect by rn-1 = prior rn
group by no;
[/PHP]

使用道具 举报

回复
招聘 : 系统分析师
论坛徽章:
483
马上有钱
日期:2014-02-19 11:55:14itpub13周年纪念徽章
日期:2014-09-29 01:14:14itpub13周年纪念徽章
日期:2014-10-08 15:15:25itpub13周年纪念徽章
日期:2014-10-08 15:15:25马上有对象
日期:2014-10-12 11:58:40马上有车
日期:2014-11-16 17:11:29慢羊羊
日期:2015-02-09 17:04:38沸羊羊
日期:2015-03-04 14:43:432015年新春福章
日期:2015-03-06 11:57:31ITPUB年度最佳版主
日期:2015-03-18 15:48:48
发表于 2005-8-1 12:31 | 显示全部楼层
好东西,不过8i不支持,要9i才支持

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

DTCC2020中国数据库技术大会 限时8.5折

【架构革新 高效可控】2020年9月21日~23日第十一届中国数据库技术大会将在北京隆重召开。

大会设置2大主会场,20+技术专场,将邀请超百位行业专家,重点围绕数据架构、AI与大数据、传统企业数据库实践和国产开源数据库等内容展开分享和探讨,为广大数据领域从业人士提供一场年度盛会和交流平台。

http://dtcc.it168.com


大会官网>>
TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表