Oracle SQL层次结构或带有图节点的表数据上的递归查询

Question

这是我的表"图形"节点。每个元组代表一个无方向的边缘。

╔═════════╦═════════╗
║ NODEONE ║ NODETWO ║
╠═════════╬═════════╣
║ A       ║ A       ║
║ A       ║ A       ║
║ A       ║ B       ║
║ A       ║ B       ║
║ A       ║ A       ║
║ C       ║ D       ║
║ C       ║ A       ║
║ D       ║ E       ║
║ A       ║ E       ║
║ D       ║ A       ║
║ G       ║ K       ║
║ G       ║ G       ║
║ K       ║ K       ║
║ K       ║ L       ║
║ L       ║ M       ║
║ Y       ║ M       ║
║ G       ║ L       ║
║ G       ║ L       ║
║ X       ║ Z       ║
║ D       ║ D       ║
║ I       ║ I       ║
╚═════════╩═════════╝

您可以看到，此表中有四个不同的无向图。

1. 节点(a，b，c，d，e(
2. 节点(L，K，G，M，Y(
3. 节点(i(
4. 节点(x，z(

我尝试了类似于下面发布的查询;

select nodeone,nodetwo
from
graphtable
start with NODEONE='D'
connect by nocycle prior nodeone=nodetwo

我也可以使用递归查询来穿越图形。

但是，如果我以开始，我需要让所有与的元组一起参与其中。但是，我没有从我的任何查询中得到这一结果。

以nodeone ='a'开头;似乎返回了所有边缘，但是没有出现" d-d"。从nodeone ='d'开始;似乎没有返回以前结果附近的任何东西。

请帮助..感谢提前任何帮助。谢谢。

Answer 1

每个元组代表一个无方向的边缘。

您不将其视为无方向的边缘 - 您将其视为有向边缘，因为您仅检查prior nodeone=nodetwo，并且不检查当前边缘的任何一端是否可以匹配上一个边缘的任一端。

sql小提琴

Oracle 11G R2架构设置：

CREATE TABLE graphtable ( NODEONE, NODETWO ) AS
  SELECT 'A', 'A' FROM DUAL UNION ALL
  SELECT 'A', 'A' FROM DUAL UNION ALL
  SELECT 'A', 'B' FROM DUAL UNION ALL
  SELECT 'A', 'B' FROM DUAL UNION ALL
  SELECT 'A', 'A' FROM DUAL UNION ALL
  SELECT 'C', 'D' FROM DUAL UNION ALL
  SELECT 'C', 'A' FROM DUAL UNION ALL
  SELECT 'D', 'E' FROM DUAL UNION ALL
  SELECT 'A', 'E' FROM DUAL UNION ALL
  SELECT 'D', 'A' FROM DUAL UNION ALL
  SELECT 'G', 'K' FROM DUAL UNION ALL
  SELECT 'G', 'G' FROM DUAL UNION ALL
  SELECT 'K', 'K' FROM DUAL UNION ALL
  SELECT 'K', 'L' FROM DUAL UNION ALL
  SELECT 'L', 'M' FROM DUAL UNION ALL
  SELECT 'Y', 'M' FROM DUAL UNION ALL
  SELECT 'G', 'L' FROM DUAL UNION ALL
  SELECT 'G', 'L' FROM DUAL UNION ALL
  SELECT 'X', 'Z' FROM DUAL UNION ALL
  SELECT 'D', 'D' FROM DUAL UNION ALL
  SELECT 'I', 'I' FROM DUAL;

查询1 ：

SELECT DISTINCT
       nodeone,
       nodetwo,
       rowid    -- Included as a unique id to differentiate edges with the
                -- same start/end points.
FROM   graphtable
START WITH NODEONE = 'D'
CONNECT BY NOCYCLE
   PRIOR nodeone IN ( nodeone, nodetwo )
OR PRIOR nodetwo IN ( nodeone, nodetwo )
ORDER SIBLINGS BY nodeone, nodetwo

结果：

| NODEONE | NODETWO |                     ROWID |
|---------|---------|---------------------------|
|       A |       A | oracle.sql.ROWID@57528909 |
|       A |       A | oracle.sql.ROWID@3d7f5c9c |
|       A |       A | oracle.sql.ROWID@777a44ea |
|       A |       B | oracle.sql.ROWID@1ca773d6 |
|       A |       B | oracle.sql.ROWID@5f7ebb8a |
|       A |       E | oracle.sql.ROWID@18229745 |
|       C |       A | oracle.sql.ROWID@3d5acdbf |
|       C |       D | oracle.sql.ROWID@1ac42001 |
|       D |       A | oracle.sql.ROWID@30cc6a38 |
|       D |       D | oracle.sql.ROWID@3cd85bdb |
|       D |       E | oracle.sql.ROWID@57845eca |