给定一个非常简单的表，例如：
-- SQLite3
CREATE TABLE tst (
id INTEGER PRIMARY KEY AUTOINCREMENT,
parent_id INTEGER CHECK (parent_id <> id),
tag STRING NOT NULL,
FOREIGN KEY (parent_id) REFERENCES tst(id)
)
我可以使用WITH RECURSIVE（公用表表达式）从任何节点向上到达该树的“根”，或从一个节点向下遍历到其所有子级（沿着所有分支）。以下是似乎分别适用于这两种情况的查询：
WITH RECURSIVE t(id, parent_id, tag) AS (
SELECT id, parent_id, tag FROM tst WHERE id=:mynode
UNION ALL
SELECT t2.id, t2.parent_id, t2.tag FROM tst AS t2
JOIN t ON t.parent_id = t2.id
) SELECT * FROM t
... 和：
WITH RECURSIVE t(id, parent_id, tag) AS (
SELECT id, parent_id, tag FROM tst WHERE id=?
UNION ALL
SELECT t2.id, t2.parent_id, t2.tag FROM tst AS t2
JOIN t ON t.id = t2.parent_id
) SELECT * FROM t
（我所做的只是相反的t.parent_id，t2.id从第一个示例到另一个示例）。
就像魅力一样。但是我试图把头从任何节点开始并获得整个行组。
显而易见的解决方法是执行第一个查询，找到要在parent_id IS NULL其上执行第二个查询的行。但是我认为必须有一个更优雅的解决方案。
它是什么？
我发现我以前的RCTE查询可以工作，但是对我的应用程序有两个主要缺陷。
ORDER BY子句完全没有基础...因此，即使我根据其嵌套深度缩进每一行，生成的“概述”也将完全错误。这个稍微复杂一些的查询似乎解决了这两个问题：
WITH RECURSIVE tree (id, parent_id, tag, depth, path) AS (
SELECT id, parent_id, tag, 1 AS depth, '' AS path FROM tst WHERE id = (
WITH RECURSIVE t3 (id, parent_id) AS (
SELECT id, parent_id FROM tst WHERE id = :mynode
UNION ALL
SELECT t2.id, t2.parent_id FROM tst AS t2
JOIN t3 ON t3.parent_id=t2.id
) SELECT id FROM t3 WHERE parent_id IS NULL
) UNION ALL
SELECT t2.id, t2.parent_id, t2.tag, tree.depth+1,
path || '/' || CAST(t2.id AS VARCHAR) FROM tst AS t2
JOIN tree ON tree.id = t2.parent_id
) SELECT * FROM tree ORDER by path;
...这样看来似乎不允许我在此处标记代码的内容...但我将depthandpath列添加到“树”（CTE虚拟）表中，为这些（虚拟）表提供初始值我的第一个列SELECT（使用1 AS depth, '' AS path（这对我来说是个新技巧），然后在每次递归操作中使用修改tree.depth+1, path || '/' || CAST(t2.id AS VARCHAR)；然后，最后我可以使用它path，ORDER BY并使用应用程序中的深度为每行添加前缀适当程度的缩进。
为了使此功能适用于我的应用程序，我可以执行以下操作：
#!python
for each in db.execute("SELECT id FROM tst WHERE parent_id IS NULL").fetchall():
for row in db.execute(qry, each):
print("%s\t%s%s" % (row[0], ' ' * row[3], row[2]))
...qry我在上面描述的查询在哪里（实际上已调整为仅获取感兴趣的列，但该示例即使在*那儿也可以使用）。在实践中，我可能会使用LIMIT和OFFSET来浏览这些结果（就像我已经为不支持任何消息线程的表中的结果列表所做的那样）。
我也知道，为此，CHECK我放在表架构上只是防止了最简单的循环树形式。似乎parent_id INTEGER CHECK (parent_id IS NULL or parent_id < id)应该更好地工作。（parent_id-> id链接的每个链必须单调递减...因此不可能循环。FOREIGN KEY强制执行该属性的INSERT语句已经...但是此检查强制也适用UPDATE。（从技术上讲，我应该使用“日期”字段添加到我的实际应用中，但我希望代理键就足够了）。
顺便说一句：为此帖子大声喊：a_horse_with_no_name：https ://dba.stackexchange.com/a/7150 ...这帮助我弄清楚了如何构建路径。
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句