SQL选择一行的后代 [英] SQL select descendants of a row

问题描述

假设一个树结构在 SQL 中是这样实现的:

Suppose a tree structure is implemented in SQL like this:

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

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

Although cycles can be created in this representation, let's assume we never let that happen. The table will only store a collection of roots (records where parent is null) and their descendants.

目标是在给定表上节点的 id 的情况下，找到它的所有后代节点.

The goal is to, given an id of a node on the table, find all nodes that are descendants of it.

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

A is a descendant of B if either A's parent is B or A's parent is a descendant of B. Note the recursive definition.

以下是一些示例数据:

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 的(直接)孩子:

We can select the (immediate) children of 1 by doing this:

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

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

We can select the children and grandchildren of 1 by doing this:

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的孩子、孙子和曾孙:

We can select the children, grandchildren, and great grandchildren of 1 by doing this:

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 的所有后代，而不是那些处于固定深度的后代?似乎我需要创建一个递归查询或其他东西.

How can a query be constructed that gets all descendants of node 1 rather than those at a fixed depth? It seems like I would need to create a recursive query or something.

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

I'd like to know if such a query would be possible using SQLite. However, if this type of query requires features not available in SQLite, I'm curious to know if it can be done in other SQL databases.

推荐答案

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

Some databases allow that using recursive common table expressions, but not SQLite.

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

You could consider changing your table definition. With a table like this, it's easy to query all descendants of 1:

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

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

This allows you to query all descendants of 1 like:

SELECT * FROM YourTable WHERE id LIKE '001%'

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

It sounds a bit whacky, but works very well in practice.

这篇关于SQL选择一行的后代的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！