现在MySQL 8.0支持递归查询，我们可以说所有流行的SQL数据库都支持标准语法的递归查询。
WITH RECURSIVE MyTree AS (
SELECT * FROM MyTable WHERE ParentId IS NULL
UNION ALL
SELECT m.* FROM MyTABLE AS m JOIN MyTree AS t ON m.ParentId = t.Id
)
SELECT * FROM MyTree;
我在2017年的演讲中测试了MySQL 8.0中的递归查询。
以下是我2008年的原始答案:
有几种方法可以在关系数据库中存储树形结构的数据。您在示例中展示的内容使用了两个方法:
邻接表(“父”列)和
路径枚举(名称列中的虚线数字)。
另一种解决方案称为嵌套集，它也可以存储在同一个表中。阅读Joe Celko的“Smarties SQL中的树和层次结构”，了解更多关于这些设计的信息。
我通常更喜欢一种称为闭包表(又名“邻接关系”)的设计来存储树状结构的数据。它需要另一个表，但是查询树非常简单。
我在演示中用SQL和PHP构建分层数据模型时提到了闭包表，在我的书《SQL反模式第1卷:避免数据库编程的陷阱》中也提到了闭包表。
CREATE TABLE ClosureTable (
ancestor_id INT NOT NULL REFERENCES FlatTable(id),
descendant_id INT NOT NULL REFERENCES FlatTable(id),
PRIMARY KEY (ancestor_id, descendant_id)
);
将所有路径存储在闭包表中，其中存在从一个节点到另一个节点的直接祖先。为每个节点包含一行以引用自身。例如，使用你在问题中展示的数据集:
INSERT INTO ClosureTable (ancestor_id, descendant_id) VALUES
(1,1), (1,2), (1,4), (1,6),
(2,2), (2,4),
(3,3), (3,5),
(4,4),
(5,5),
(6,6);
现在你可以得到一个从节点1开始的树，像这样:
SELECT f.*
FROM FlatTable f
JOIN ClosureTable a ON (f.id = a.descendant_id)
WHERE a.ancestor_id = 1;
输出(在MySQL客户端中)如下所示:
+----+
| id |
+----+
| 1 |
| 2 |
| 4 |
| 6 |
+----+
换句话说，节点3和5被排除在外，因为它们是单独层次结构的一部分，而不是从节点1向下延伸。
回复:e-satis关于直系子女(或直系父母)的评论。您可以向ClosureTable添加一个“path_length”列，以便更容易查询直接的子节点或父节点(或任何其他距离)。
INSERT INTO ClosureTable (ancestor_id, descendant_id, path_length) VALUES
(1,1,0), (1,2,1), (1,4,2), (1,6,1),
(2,2,0), (2,4,1),
(3,3,0), (3,5,1),
(4,4,0),
(5,5,0),
(6,6,0);
然后，您可以在搜索中添加一个术语，以查询给定节点的直接子节点。这些是path_length为1的后代。
SELECT f.*
FROM FlatTable f
JOIN ClosureTable a ON (f.id = a.descendant_id)
WHERE a.ancestor_id = 1
AND path_length = 1;
+----+
| id |
+----+
| 2 |
| 6 |
+----+
回复来自@ashraf的评论:“把整棵树(按名字)排序怎么样?”
下面是一个示例查询，返回节点1的所有后代节点，将它们连接到包含其他节点属性(如名称)的FlatTable，并按名称排序。
SELECT f.name
FROM FlatTable f
JOIN ClosureTable a ON (f.id = a.descendant_id)
WHERE a.ancestor_id = 1
ORDER BY f.name;
关于@Nate的评论:
SELECT f.name, GROUP_CONCAT(b.ancestor_id order by b.path_length desc) AS breadcrumbs
FROM FlatTable f
JOIN ClosureTable a ON (f.id = a.descendant_id)
JOIN ClosureTable b ON (b.descendant_id = a.descendant_id)
WHERE a.ancestor_id = 1
GROUP BY a.descendant_id
ORDER BY f.name
+------------+-------------+
| name | breadcrumbs |
+------------+-------------+
| Node 1 | 1 |
| Node 1.1 | 1,2 |
| Node 1.1.1 | 1,2,4 |
| Node 1.2 | 1,6 |
+------------+-------------+
一位用户建议今天进行编辑。版主批准了编辑，但我要撤销它。
编辑建议上面最后一个查询中的ORDER BY应该是ORDER BY b.path_length, f.name，大概是为了确保顺序与层次结构匹配。但这行不通，因为它会将“Node 1.1.1”排在“Node 1.2”之后。
如果您希望排序以合理的方式匹配层次结构，这是可能的，但不是简单地按路径长度排序。例如，请参阅我对MySQL闭包表分层数据库的回答-如何以正确的顺序提取信息。
