SQL选择一行的后代

假设在SQL中实现树结构,如下所示:

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

尽管可以在此表示中创建循环,但我们假设我们永远不会让这种情况发生.该表只存储一个根集合(父节点为null的记录)及其后代.

目标是,给定表中节点的id,找到作为其后代的所有节点.

阿是的后代乙如果任一个的父是乙或甲的父是的后代乙.注意递归定义.

以下是一些示例数据:

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数据库中完成.

有些数据库允许使用递归公用表表达式,但不能使用SQLLite.

您可以考虑更改表定义.使用这样的表,可以轻松查询1的所有后代:

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

这允许您查询1的所有后代,如:

SELECT * FROM YourTable WHERE id LIKE '001%'

这听起来有点笨拙,但在实践中效果很好.

您设置架构的方式并不能很好地适应关系模型(正如您所发现的那样).但是另一种方式起初可能并不那么明显,但更灵活.它被称为"嵌套集合模型",它只是略微不同地构造事物.

在MySQL中管理分层数据(查看"嵌套集模型"部分)显示了如何在MySQL中执行此操作,但它应该相当容易地转换为SQLite.我不会在这里详细介绍,因为那篇文章实际上很长,但它应该给你所有你需要的东西.