同一个表上的嵌套连接（树结构）

Question

我的日期是按树形结构组织的。

以下适用（Oracle SQL 语法）：

CREATE TABLE TREE
(
  NAME VARCHAR2(20),
  ID NUMBER(10, 0),
  PARENT NUMBER(10, 0)
)
;

INSERT INTO "TREE" (NAME, ID) VALUES ('a', '1');
INSERT INTO "TREE" (NAME, ID, PARENT) VALUES ('a.1', '2', '1');
INSERT INTO "TREE" (NAME, ID, PARENT) VALUES ('a.2', '3', '1');
INSERT INTO "TREE" (NAME, ID, PARENT) VALUES ('a.2.1', '4', '3');
INSERT INTO "TREE" (NAME, ID, PARENT) VALUES ('a.2.2', '5', '3');
INSERT INTO "TREE" (NAME, ID) VALUES ('b', '6');

我想按 id 返回完整的树，因此对于查询：

select name, id <<<TODO LOGIC>> where id = 1

我会得到

|  name  |  id  |
|  a     |  1   |
|  a.1   |  2   |
|  a.2   |  3   |
|  a.2.1 |  4   |
|  a.2.2 |  5   |

一个子树 我会得到：

select name, id <<<TODO LOGIC>> where id = 3

我会得到

|  name  |  id  |
|  a.2   |  3   |
|  a.2.1 |  4   |
|  a.2.2 |  5   |

其中，对于平面条目 b，它将得到

select name, id <<<TODO LOGIC>> where id = 6

我会似乎

|  name  |  id  |
|  b     |  6   |

简单的省略连接查询无法实现此目的，或者我错过了什么？

以下查询确实返回完整结构，但是当开始使用 where 语句进行过滤时，它会失败。

select t1.id t1Id, t2.id t2Id, t1.name t1Name, t2.name t2Name from tree t1 left outer join tree t2 on t1.id = t2.parent

Answer 1

当您具有树结构时，您可能需要分层查询。如下：

 select t.*
   from tree t
connect by prior t.id = t.parent
  start with t.id = :id
  order siblings by t.id

请参阅分层查询 了解详情。

Answer 2

您可以在 Oracle 上使用 start with - connect by 语法。如果我没记错的话，事情是这样的

select * from Tree t
start with t.ID = 1 connect by prior t.ID = t.Parent

，但我没有 Oracle 可以立即检查它。也许它的之前的 t.Parent = t.ID。请注意，有时会很慢，请谨慎使用。

另一种方法是创建表来存储节点之间的所有间接关系（不仅是aa.1，还包括aa.2.1等）。您可以使用 PL/SQL 递归存储过程来填充它。两种方法：

1. 简单的方法是创建一个程序来完成间接表的完全重新填充。您可以在运行报告之前调用它。

2. 如果你需要即时效果，你应该编写重新填充程序，以便它只更新树中一条记录的间接关系。然后，您禁止对 Tree 进行直接插入和更新，并强制它们通过存储的 PL/SQL 过程（如 InsertTree/UpdateTree）进行，而该过程又会调用过程来更新具有间接关系的表。

Answer 3

为此，您可以使用union，并且需要限制树的深度，以便可以在一个查询中选择它。

SELECT id, name
FROM TREE as node
WHERE 
  node.id = :id
UNION
SELECT child1.id, child1.name
FROM TREE as node
  inner join TREE as child1 on node.id = child1.parent
WHERE 
  node.id = :id
UNION
SELECT child2.id, child2.name
FROM TREE as node
  inner join TREE as child1 on node.id = child1.parent
  inner join TREE as child2 on child1.id = child2.parent
WHERE 
  node.id = :id

这里的问题是，SQL 在递归方面非常糟糕（而关系结构在这方面实际上很棒）。

要使其完全动态，请对树中的每个级别使用查询，或者使用数据库引擎特定的 SQL 扩展（如果有任何可用的内容）。