需求:已知某个id的值,需要查询这个id关联的所有父级的数据,如查询到该id的父类数据之后,还需要查询父类数据的爷爷辈数据,依次类推,直到查询到顶级。
SELECT T2.*
FROM (
SELECT
@r AS _id,
(SELECT @r := parent_id FROM folder WHERE id = _id) AS parent_id,
@l := @l + 1 AS lvl
FROM
(SELECT @r := '{你的id}', @l := 0) vars,
folder h
WHERE @r <> 0) T1
JOIN folder T2
ON T1._id = T2.id
ORDER BY T1.lvl DESC
-- folder为需要递归查询的表
-- {你的id}为某个id的数据
表folder的结构(主要有可递归的字段id和parent_id):
查询结果:
界面效果: