查看: 12031|回复: 38

[精华] 求一SQL算法【找团伙】

[复制链接]
论坛徽章:
1
优秀写手
日期:2014-11-05 06:00:03
发表于 2009-11-3 18:20 | 显示全部楼层 |阅读模式
本帖最后由 lastwinner 于 2013-3-26 02:06 编辑

现有表test_a,有字段col_a,col_b,其值如下
a1    a4
a1    a3
a4    a2
a3    a6
a4    a1
a4    a8
a10   a5
a5    a9

需要得到结果
a1,a3,a4,a2,a6,a8为一集合
a5,a9,a10为一集合

意思就是找出col_a与其相联系的其它列作为一个集合,集合之间没有交集
论坛徽章:
0
发表于 2009-11-3 19:22 | 显示全部楼层
想用connect by做,好象不行。期待高手

使用道具 举报

回复
论坛徽章:
403
紫蛋头
日期:2012-05-21 10:19:41迷宫蛋
日期:2012-06-06 16:02:49奥运会纪念徽章:足球
日期:2012-06-29 15:30:06奥运会纪念徽章:排球
日期:2012-07-10 21:24:24鲜花蛋
日期:2012-07-16 15:24:59奥运会纪念徽章:拳击
日期:2012-08-07 10:54:50奥运会纪念徽章:羽毛球
日期:2012-08-21 15:55:33奥运会纪念徽章:蹦床
日期:2012-08-21 21:09:51奥运会纪念徽章:篮球
日期:2012-08-24 10:29:11奥运会纪念徽章:体操
日期:2012-09-07 16:40:00
发表于 2009-11-3 20:17 | 显示全部楼层
不太容易
我只能做到
with tmp as(
select 1 rn,'a1'l,'a4'r from dual union all
select 2,'a1','a3' from dual union all
select 3,'a4','a2' from dual union all
select 4,'a3','a6' from dual union all
select 5,'a4','a1' from dual union all
select 6,'a4','a8' from dual union all
select 7,'a10','a5' from dual union all
select 8,'a5','a9' from dual
),
t1 as(
select max(sys_connect_by_path(l,' '))s,l
from (select r,l from tmp union select l,r from tmp)
connect by nocycle prior l=r
group by l
order by 1
)
select s,l from t1;
SQL> with tmp as(
  2  select 1 rn,'a1'l,'a4'r from dual union all
  3  select 2,'a1','a3' from dual union all
  4  select 3,'a4','a2' from dual union all
  5  select 4,'a3','a6' from dual union all
  6  select 5,'a4','a1' from dual union all
  7  select 6,'a4','a8' from dual union all
  8  select 7,'a10','a5' from dual union all
  9  select 8,'a5','a9' from dual
10  ),
11  t1 as(
12  select max(sys_connect_by_path(l,' '))s,l
13  from (select r,l from tmp union select l,r from tmp)
14  connect by nocycle prior l=r
15  group by l
16  order by 1
17  )
18  select s,l from t1;

S                              L
------------------------------ ---
a8                            a8
a8 a4                         a4
a8 a4 a1                      a1
a8 a4 a1 a3                   a3
a8 a4 a1 a3 a6                a6
a8 a4 a2                      a2
a9                            a9
a9 a5                         a5
a9 a5 a10                     a10

9 rows selected.

使用道具 举报

回复
论坛徽章:
403
紫蛋头
日期:2012-05-21 10:19:41迷宫蛋
日期:2012-06-06 16:02:49奥运会纪念徽章:足球
日期:2012-06-29 15:30:06奥运会纪念徽章:排球
日期:2012-07-10 21:24:24鲜花蛋
日期:2012-07-16 15:24:59奥运会纪念徽章:拳击
日期:2012-08-07 10:54:50奥运会纪念徽章:羽毛球
日期:2012-08-21 15:55:33奥运会纪念徽章:蹦床
日期:2012-08-21 21:09:51奥运会纪念徽章:篮球
日期:2012-08-24 10:29:11奥运会纪念徽章:体操
日期:2012-09-07 16:40:00
发表于 2009-11-3 21:09 | 显示全部楼层
然后对2的结果找出不是其他行的子串
a8 a4 a1 a3 a6                a6
a8 a4 a2                      a2
a9 a5 a10                     a10

再想办法把1,2行合并,因为它们有公共子串

使用道具 举报

回复
论坛徽章:
526
奥运会纪念徽章:垒球
日期:2008-09-15 01:28:12生肖徽章2007版:鸡
日期:2008-11-17 23:40:58生肖徽章2007版:马
日期:2008-11-18 05:09:48数据库板块每日发贴之星
日期:2008-11-29 01:01:02数据库板块每日发贴之星
日期:2008-12-05 01:01:03生肖徽章2007版:虎
日期:2008-12-10 07:47:462009新春纪念徽章
日期:2009-01-04 14:52:28数据库板块每日发贴之星
日期:2009-02-08 01:01:03生肖徽章2007版:蛇
日期:2009-03-09 22:18:532009日食纪念
日期:2009-07-22 09:30:00
发表于 2009-11-4 02:57 | 显示全部楼层
只好用PLSQL了:
CREATE TABLE TEST AS
select 'a1' l,'a4' r from dual union all
select 'a1','a3' from dual union all
select 'a4','a2' from dual union all
select 'a3','a6' from dual union all
select 'a4','a1' from dual union all
select 'a4','a8' from dual union all
select 'a10','a5' from dual union all
select 'a5','a9' from dual
;


CREATE TABLE t_res (node VARCHAR2(10),lvl NUMBER,group_id NUMBER);

DECLARE
  l_level NUMBER;
  l_group_id NUMBER :=1;
BEGIN
   
   LOOP
      l_level:=1;
      
      INSERT INTO t_res
      WITH v AS (
         SELECT l,r
           FROM test
          WHERE NOT EXISTS (SELECT 1 FROM t_res WHERE t_res.node IN (l,r))
                AND ROWNUM=1
      )
      SELECT l,l_level,l_group_id
        FROM v
      UNION
      SELECT r,l_level,l_group_id
        FROM v;
      
      EXIT WHEN SQL%ROWCOUNT=0;

      LOOP
         MERGE INTO t_res
         USING (SELECT DISTINCT (CASE WHEN node=l THEN r ELSE l END) node
                  FROM test,(SELECT node FROM t_res WHERE lvl=l_level AND group_id=l_group_id)
                 WHERE node IN (test.l,test.r)
                ) n
         ON (t_res.node=n.node)
         WHEN NOT MATCHED THEN INSERT VALUES (n.node,l_level+1,l_group_id);
               
         EXIT WHEN SQL%ROWCOUNT=0;
         l_level := l_level+1;
      END LOOP;
      l_group_id := l_group_id+1;
  END LOOP;
END;
/

select * from t_res ORDER BY group_id,lvl;

NODE              LVL   GROUP_ID
---------- ---------- ----------
a4                  1          1
a1                  1          1
a3                  2          1
a2                  2          1
a8                  2          1
a6                  3          1
a10                 1          2
a5                  1          2
a9                  2          2

使用道具 举报

回复
论坛徽章:
403
紫蛋头
日期:2012-05-21 10:19:41迷宫蛋
日期:2012-06-06 16:02:49奥运会纪念徽章:足球
日期:2012-06-29 15:30:06奥运会纪念徽章:排球
日期:2012-07-10 21:24:24鲜花蛋
日期:2012-07-16 15:24:59奥运会纪念徽章:拳击
日期:2012-08-07 10:54:50奥运会纪念徽章:羽毛球
日期:2012-08-21 15:55:33奥运会纪念徽章:蹦床
日期:2012-08-21 21:09:51奥运会纪念徽章:篮球
日期:2012-08-24 10:29:11奥运会纪念徽章:体操
日期:2012-09-07 16:40:00
发表于 2009-11-4 08:38 | 显示全部楼层

回复 #5 newkid 的帖子

记录学习

使用道具 举报

回复
论坛徽章:
1
优秀写手
日期:2014-11-05 06:00:03
 楼主| 发表于 2009-11-4 09:30 | 显示全部楼层

回复 #5 newkid 的帖子

感谢你的思想和辛苦,小弟万分感激。对你的算法真是受易非浅。

使用道具 举报

回复
论坛徽章:
6
CTO参与奖
日期:2009-02-20 09:44:20数据库板块每日发贴之星
日期:2009-04-02 01:01:032009日食纪念
日期:2009-07-22 09:30:002010新春纪念徽章
日期:2010-03-01 11:21:02咸鸭蛋
日期:2011-08-29 08:45:46ITPUB十周年纪念徽章
日期:2011-11-01 16:20:28
发表于 2009-11-4 13:49 | 显示全部楼层
WITH TEMP AS
(
SELECT 'a1'  COL_A,'a4' COL_B FROM DUAL UNION
SELECT 'a1'  COL_A,'a3' COL_B FROM DUAL UNION
SELECT 'a4'  COL_A,'a2' COL_B FROM DUAL UNION
SELECT 'a3'  COL_A,'a6' COL_B FROM DUAL UNION
SELECT 'a4'  COL_A,'a1' COL_B FROM DUAL UNION
SELECT 'a4'  COL_A,'a8' COL_B FROM DUAL UNION
SELECT 'a10' COL_A,'a5' COL_B FROM DUAL UNION
SELECT 'a5'  COL_A,'a9' COL_B FROM DUAL
)
SELECT MAX(TEXT1) TEXT FROM
(SELECT WMSYS.WM_CONCAT(TEXT) OVER(PARTITION BY GROP ORDER BY RN1) TEXT1,T.* FROM
  (SELECT TEXT,RN1,SUM(GROP) OVER(ORDER BY RN1) GROP,ROW_NUMBER() OVER(PARTITION BY TEXT ORDER BY RN1) RN2 FROM
    (SELECT COL_B TEXT,ROWNUM RN1,DECODE(LEVEL,1,1,0) GROP FROM
      (SELECT NULL COL_A,COL_A COL_B FROM TEMP GROUP BY COL_A
       UNION
       SELECT * FROM TEMP
      ) CONNECT BY NOCYCLE COL_A = PRIOR COL_B START WITH COL_A IS NULL
    ) T ORDER BY RN1
  ) T WHERE RN2 = 1
  ) GROUP BY GROP

1        a1,a3,a6,a4,a2,a8
2        a10,a5,a9

使用道具 举报

回复
论坛徽章:
17
生肖徽章2007版:鸡
日期:2008-01-02 17:35:53ITPUB十周年纪念徽章
日期:2011-11-01 16:21:152011新春纪念徽章
日期:2011-02-18 11:43:332010新春纪念徽章
日期:2010-03-01 11:04:57生肖徽章2007版:鼠
日期:2009-11-29 12:48:34生肖徽章2007版:兔
日期:2009-11-23 16:38:24祖国60周年纪念徽章
日期:2009-10-09 08:28:00生肖徽章2007版:龙
日期:2009-09-10 11:23:342009日食纪念
日期:2009-07-22 09:30:00生肖徽章2007版:猴
日期:2009-06-14 03:09:34
发表于 2009-11-4 14:11 | 显示全部楼层
mark,佩服

使用道具 举报

回复
论坛徽章:
403
紫蛋头
日期:2012-05-21 10:19:41迷宫蛋
日期:2012-06-06 16:02:49奥运会纪念徽章:足球
日期:2012-06-29 15:30:06奥运会纪念徽章:排球
日期:2012-07-10 21:24:24鲜花蛋
日期:2012-07-16 15:24:59奥运会纪念徽章:拳击
日期:2012-08-07 10:54:50奥运会纪念徽章:羽毛球
日期:2012-08-21 15:55:33奥运会纪念徽章:蹦床
日期:2012-08-21 21:09:51奥运会纪念徽章:篮球
日期:2012-08-24 10:29:11奥运会纪念徽章:体操
日期:2012-09-07 16:40:00
发表于 2009-11-4 14:36 | 显示全部楼层

回复 #8 chen222246lei 的帖子

connect和分析函数结合,好,改写一下,不使用WMSYS.WM_CONCAT
WITH TEMP AS
(
SELECT 'a1'  COL_A,'a4' COL_B FROM DUAL UNION
SELECT 'a1'  COL_A,'a3' COL_B FROM DUAL UNION
SELECT 'a4'  COL_A,'a2' COL_B FROM DUAL UNION
SELECT 'a3'  COL_A,'a6' COL_B FROM DUAL UNION
SELECT 'a4'  COL_A,'a1' COL_B FROM DUAL UNION
SELECT 'a4'  COL_A,'a8' COL_B FROM DUAL UNION
SELECT 'a10' COL_A,'a5' COL_B FROM DUAL UNION
SELECT 'a5'  COL_A,'a9' COL_B FROM DUAL
)
--SELECT MAX(TEXT1) TEXT FROM
--(SELECT WMSYS.WM_CONCAT(TEXT) OVER(PARTITION BY GROP ORDER BY RN1) TEXT1,T.* FROM
select  max(sys_connect_by_path(text,',')) from
  (select * from
  (SELECT TEXT,RN1,SUM(GROP) OVER(ORDER BY RN1) GROP,ROW_NUMBER() OVER(PARTITION BY TEXT ORDER BY RN1) RN2 FROM
    (SELECT COL_B TEXT,ROWNUM RN1,DECODE(LEVEL,1,1,0) GROP FROM
      (SELECT NULL COL_A,COL_A COL_B FROM TEMP GROUP BY COL_A
       UNION
       SELECT * FROM TEMP
      ) CONNECT BY NOCYCLE COL_A = PRIOR COL_B START WITH COL_A IS NULL
    ) T ORDER BY RN1
  ) T WHERE RN2 = 1)
  connect by prior rn1=rn1+1 and prior grop=grop
  GROUP BY GROP

[ 本帖最后由 〇〇 于 2009-11-4 15:33 编辑 ]

使用道具 举报

回复

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

本版积分规则 发表回复

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

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

大会设置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号
  
快速回复 返回顶部 返回列表