求一SQL算法【找团伙】

fenglixiang

能否请oo讲解一下为什么
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

我死活想不出来你是怎么想出来要这两列的。

newkid

DECODE(LEVEL,1,1,0)其实是求根，SUM(GROP) OVER(ORDER BY RN1)是为了保证同一个根遍历出来的，看我改过的版本要清晰得多。
ROW_NUMBER() OVER(PARTITION BY TEXT ORDER BY RN1) RN2这个是他这个思路的精髓，就是一个元素只关心第一次出现，（整个集合就像一个圆圈，从哪里开始都一样，后面再次出现则前面肯定被其他遍历所包含）

newkid

原帖由 nyfor 于 2009-11-5 09:35 发表
……昨晚洗澡的時候想到了其中的漏洞.……

佩服佩服，连FB的时候都没忘了技术！

ilovemilk

没有仔细研究过，学习

fenglixiang

ROW_NUMBER() OVER(PARTITION BY TEXT ORDER BY RN1) 这个我怎么加总觉得有问题，是不是改成
ROW_NUMBER() OVER(PARTITION BY TEXT ORDER BY rt)好一些？？从而保证每棵树的节点都选出来。

chrisleeali

mark

newkid

原帖由 fenglixiang 于 2009-11-7 18:56 发表
ROW_NUMBER() OVER(PARTITION BY TEXT ORDER BY RN1) 这个我怎么加总觉得有问题，是不是改成
ROW_NUMBER() OVER(PARTITION BY TEXT ORDER BY rt)好一些？？从而保证每棵树的节点都选出来。

你这个是不对的。
一个根包含了整个集合，我们好不容易把它们圈到一起（下一步就是GROUP BY ROOT）. ROW_NUMBER()在这里是为了过滤重复的集合。
按你的方法，最后又按不同的ROOT剥离开，等于前功尽弃。

DAWN_WU

学习~~

lastwinner

回帖精彩，加个精华

lastwinner

顺道将我在 http://www.itpub.net/thread-1775413-1-1.html 的解法贴过来

1. with u as(
   
2. select 111 a,      222 b from dual
   
3. union
   
4. select 111,      333 from dual
   
5. union
   
6. select 333,      444 from dual
   
7. union
   
8. select 555,      666 from dual
   
9. union
   
10. select 555,      777 from dual
    
11. union
    
12. select 888,      666 from dual
    
13. ),
    
14. t as (select a,b from u union select b,a from u),
    
15. s as (select a,b, level lvl, sys_connect_by_path(a,',') con_str from t connect by nocycle prior a =b),
    
16. r as (select regexp_substr(con_str,'\d{3}') root, max(con_str) max_con_str, max(lvl) max_lvl from s group by regexp_substr(con_str,'\d{3}')),
    
17. q as (select x.root, x.max_con_str, x.max_lvl from r x, r y where x.root<>y.root and instr(x.max_con_str, y.max_con_str)>0 group by x.root, x.max_con_str, x.max_lvl),
    
18. p as (select x.root, x.max_con_str, x.max_lvl from q x, q y where x.root<>y.root and instr(x.max_con_str,y.root)>0),
    
19. o as (select root, p1.rn, regexp_substr(max_con_str,'\d{3}',1,p1.rn) ele, max_lvl from p, (select rownum rn from dual connect by rownum<=(select max(max_lvl) from p)) p1 where p1.rn<=max_lvl),
    
20. n as (select root, listagg(ele,',')within group(order by ele) recombine from o group by root)
    
21. select recombine from n group by recombine
    
22. /
    
23. 
    
24. RECOMBINE
    
25. 555,666,777,888
    
26. 111,222,333,444

复制代码