我有一个简单的MysqL表,它包含一个类别列表,级别由parent_id决定：
id name parent_id --------------------------- 1 Home 0 2 About 1 3 Contact 1 4 Legal 2 5 Privacy 4 6 Products 1 7 Support 1
我试图做一个面包屑的踪迹.所以我有孩子的’id’,我想要所有可用的父母(迭代链,直到我们达到0“家”).可能有任何数字或子行成为无限深度.
解决方法
改编自
here：
SELECT T2.id,T2.name
FROM (
SELECT
@r AS _id,(SELECT @r := parent_id FROM table1 WHERE id = _id) AS parent_id,@l := @l + 1 AS lvl
FROM
(SELECT @r := 5,@l := 0) vars,table1 h
WHERE @r <> 0) T1
JOIN table1 T2
ON T1._id = T2.id
ORDER BY T1.lvl DESC
1,'Home' 2,'About' 4,'Legal' 5,'Privacy'
