sql - 如何有效地检索树中节点的路径(与帖子 'parse a flat table into a tree?' 相关)

Question

这个问题是对这篇文章的跟进:

What is the most efficient/elegant way to parse a flat table into a tree?

我喜欢 ClosureMap 解决方案，但我还有一个问题需要解决。

如何轻松检索到树中特定节点的路径？例如，如果您查看提供的树:

ID 节点名称

1 '节点 1'
2 '节点 1.1'
3 '节点 2'
4 '节点 1.1.1'
5 '节点 2.1'
6 '节点 1.2'

1.1.1 的路径是:

ID = 1, 2, 4

如果不进行递归 SQL 调用，是否有一种优雅的方法来检索路径？

Answer 1

SELECT ancestor_id
FROM ClosureTable
WHERE descendant_id = 4;

返回值 1、2、4。但是它们在不同的行中返回，并且它们没有指示它们的顺序是否正确(我们可能不假设数字顺序对应于树层次结构顺序)。

有时您还会将每条路径的深度存储在 ClosureTable 中。 .但即使没有，您也可以计算给定节点有多少个祖先，并使用它进行排序:

SELECT ct1.ancestor_id, COUNT(*) AS depth
FROM ClosureTable ct1
 JOIN ClosureTable ct2 ON (ct1.ancestor_id = ct2.descendant_id)
WHERE ct1.descendant_id = 4
GROUP BY ct1.ancestor_id
ORDER BY depth;

是的，这仍然会在三行中返回结果。如果您使用 MySQL，您可以访问 GROUP_CONCAT() .否则很容易获取三行并在应用程序代码中连接它们的值。