sql - Oracle:分层查询中的编号组

Question

在我的 Oracle 数据库中，我有一个表定义了前导/后继的层次结构，它既可以分支也可以循环。我附上了一个 SQL fiddle 来演示该表是如何工作的。我的意图是为每棵孤立的树分配它自己的编号。请参阅下图以了解所需结果(请注意，在此图片中，成员被命名为 a,b,c,d... 而在附带的 fiddle 中，它们的编号为 1 ,2,3,4...):

http://sqlfiddle.com/#!4/4c887d/4/0

我还没有想出如何构建这样的查询，此时此刻我非常绝望。任何帮助，甚至指向解决方案的指针 - 任何类型的输入 - 都将不胜感激。

提前谢谢大家。

Answer 1

那些不是层次结构；它们是有向图。您仍然可以在 Oracle 中使用 CONNECT BY 来使用它们，但是如果没有根节点来START WITH，如果您的数据集很大，性能可能会出现问题。

无论如何，您需要做的是CONNECT BY NOCYCLE，而不是START WITH。这将从每个单独的节点开始计算一棵树。然后，为每个节点获取 CONNECT_BY_ROOT 并为每个不同的节点值获取 MIN()。

这是一个带有数据的工作示例。它比它需要的更复杂，因为没有地方可以获得所有节点的完整列表，所以你必须将每个顶点分成两行(一个有 from_node，一个有 to_node)以确保你包括所有这些。

CREATE TABLE tbl_tst ( from_node VARCHAR2(1), to_node VARCHAR2(1) );

INSERT INTO tbl_tst VALUES ( 'a', 'b');
INSERT INTO tbl_tst VALUES ( 'b', 'c');
INSERT INTO tbl_tst VALUES ( 'b', 'd');
INSERT INTO tbl_tst VALUES ( 'd', 'a');
INSERT INTO tbl_tst VALUES ( 'd', 'e');

INSERT INTO tbl_tst VALUES ( 'f', 'g');
INSERT INTO tbl_tst VALUES ( 'g', 'h');
INSERT INTO tbl_tst VALUES ( 'h', 'f');

INSERT INTO tbl_tst VALUES ( 'i', 'j');

COMMIT;


WITH groups AS (
SELECT DISTINCT 
       tt.from_node, 
       tt.to_node, 
       MIN(CONNECT_BY_ROOT(from_node)) 
             OVER ( PARTITION BY tt.from_node) group_min
FROM   tbl_tst tt
CONNECT BY NOCYCLE from_node = PRIOR to_node
              OR   to_node = PRIOR from_node
)
SELECT DENSE_RANK() OVER ( ORDER BY group_min ) group_number,
       DECODE(splitter.rn,1,groups.from_node,2,groups.to_node) node       
FROM groups 
CROSS JOIN ( SELECT rownum rn FROM dual CONNECT BY rownum <= 2 ) splitter
GROUP BY DECODE(splitter.rn,1,groups.from_node,2,groups.to_node),
         group_min
ORDER BY group_min, node;

+--------------+------+
| GROUP_NUMBER | NODE |
+--------------+------+
|            1 | a    |
|            1 | b    |
|            1 | c    |
|            1 | d    |
|            1 | e    |
|            2 | f    |
|            2 | g    |
|            2 | h    |
|            3 | i    |
|            3 | j    |
+--------------+------+