SQL 选择一行的后代

Question

假设在 SQL 中实现了一个树结构，如下所示：

CREATE TABLE nodes (
    id INTEGER PRIMARY KEY,
    parent INTEGER -- references nodes(id)
);

虽然可以在此表示中创建循环，但我们假设我们永远不会让这种情况发生。该表将仅存储根（父级为空的记录）及其后代的集合。

目标是，给定表中节点的 id，找到其后代的所有节点。

如果 A 的父级是 B 或 A，则 A 是 B 的后代> 的父级是B 的后代。注意递归定义。

下面是一些示例数据：

INSERT INTO nodes VALUES (1, NULL);
INSERT INTO nodes VALUES (2, 1);
INSERT INTO nodes VALUES (3, 2);
INSERT INTO nodes VALUES (4, 3);
INSERT INTO nodes VALUES (5, 3);
INSERT INTO nodes VALUES (6, 2);

它表示：

1
`-- 2
    |-- 3
    |   |-- 4
    |   `-- 5
    |
    `-- 6

我们可以通过这样做来选择 1 的（直接）子代：

SELECT a.* FROM nodes AS a WHERE parent=1;

我们可以通过这样做来选择 1 的子代和孙代：

SELECT a.* FROM nodes AS a WHERE parent=1
UNION ALL
SELECT b.* FROM nodes AS a, nodes AS b WHERE a.parent=1 AND b.parent=a.id;

我们可以通过执行以下操作来选择 1 的子代、孙子和曾孙：

SELECT a.* FROM nodes AS a WHERE parent=1
UNION ALL
SELECT b.* FROM nodes AS a, nodes AS b WHERE a.parent=1 AND b.parent=a.id
UNION ALL
SELECT c.* FROM nodes AS a, nodes AS b, nodes AS c WHERE a.parent=1 AND b.parent=a.id AND c.parent=b.id;

如何构造一个查询来获取节点 1 的所有后代而不是固定的后代深度？看来我需要创建一个递归查询或其他东西。

我想知道使用 SQLite 是否可以进行这样的查询。但是，如果这种类型的查询需要 SQLite 中不可用的功能，我很好奇是否可以在其他 SQL 数据库中完成。

Answer 1

有些数据库允许使用递归公用表表达式，但 SQLite 不允许。

您可以考虑更改表定义。使用这样的表，很容易查询 1 的所有后代：

id (varchar)
--------------
001
001002
001002003
001002003004
001002003005
001002006

这允许您查询 1 的所有后代，如下所示：

SELECT * FROM YourTable WHERE id LIKE '001%'

听起来有点古怪，但在实践中效果很好。

Answer 2

您设置模式的方式并不真正适合关系模型（正如您所发现的）。但还有另一种方法，一开始可能不那么明显，但要灵活得多。它被称为“嵌套集合模型”，只是结构略有不同。

管理 MySQL 中的分层数据（查找“嵌套”部分）设置模型”）展示了如何在 MySQL 中执行此操作，但它应该相当容易地转换为 SQLite。我不会在这里讨论太多细节，因为那篇文章实际上很长，但它应该为您提供开始所需的所有内容。

Answer 3

Oracle 有一个 CONNECT BY 语法可以用于此目的，但当然它是 Oracle 特有的。