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