MySQL：查找子树中的所有叶节点

Question

我在MySQL表中存储了一个类别的树形结构，其中包含category_id和parent_id关系。parent_id = null表示根节点。

Category (category_id, category_name, parent_id)

我想要做的是，通过节点的category_id获取所有叶子节点。我已经按照this文章中的方法进行了尝试。该文章讨论了如何使用以下查询语句获取所有叶子节点：

SELECT t1.category_name FROM
category AS t1 LEFT JOIN category as t2
ON t1.category_id = t2.parent_id
WHERE t2.category_id IS NULL;

但我正在尝试获取子树的叶节点。例如：

在上述结构中，给定节点3的结果将是：9、10、7、11、12、13。

我也尝试了这里提供的解决方案：邻接模型，给定一个id返回叶子节点。但我无法获得所需的结果。

你能帮我找到一个解决方案吗？

- Mr.code892

Answer 1

如果你的叶子节点是有序的（即任何子节点都大于其父节点），还有另一种方法：

SELECT tree.id FROM tree
LEFT JOIN tree t1 ON (t1.parent_id = tree.id)
WHERE t1.id IS NULL -- all leafs
AND tree.id > 3 -- greater than starting branch

- Bars

Answer 2

哦，好吧...我碰巧找到了一个解决方案...它有点尴尬，不过:

SELECT TRIM(RIGHT(TRIM(concat_ws(' ',
ifnull(t1.category_id,''),
ifnull(t2.category_id,''),
ifnull(t3.category_id,''),
ifnull(t4.category_id,'')
)),2)) AS leaf_node
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent_category = t1.category_id
LEFT JOIN category AS t3 ON t3.parent_category = t2.category_id
LEFT JOIN category AS t4 ON t4.parent_category = t3.category_id
WHERE t1.category_descr = 'Frames';

只要 `category_id` 小于 100，也就是只有两位数，这段代码就能正常运行。虽然这个解决方案有些疯狂、笨拙（你可以随便取一个形容词），但对我来说足矣。

- João Campos

Answer 3

你可以尝试这个脚本：

Select * from
(SELECT id,name,parent_id FROM
    (SELECT id,name,parent_id,
           CASE WHEN id in (3) THEN @idlist := CONCAT(id)
                WHEN FIND_IN_SET(parent_id,@idlist) THEN @idlist := CONCAT(@idlist,',',id)
                END as checkId
    FROM categories
    ORDER BY id ASC) as T
WHERE checkId IS NOT NULL) N1
left join 
(SELECT id,name,parent_id FROM
    (SELECT id,name,parent_id,
           CASE WHEN id in (3) THEN @idlist := CONCAT(id)
                WHEN FIND_IN_SET(parent_id,@idlist) THEN @idlist := CONCAT(@idlist,',',id)
                END as checkId
    FROM categories
    ORDER BY id ASC) as T
WHERE checkId IS NOT NULL) N2 on N1.Id = N2.Parent_Id

sqlfiddle

{{链接1：sqlfiddle}}

- Tuan