1.递归查询某一节点的无限级子集(不含自身)。
# 功能:递归查询某一节点的无限级子集。
# 参数说明:
# 表名:organization
# 父ID字段:parent_id
# 主键ID字段:id
# 顶层ID值:1604
SELECT
*
FROM
( SELECT * FROM organization ) A,
( SELECT @pv := '1604' ) B
WHERE
find_in_set( parent_id, @pv )
AND length(
@pv := concat( @pv, ',', id ))
2.反向递归查询某一节点的无限级直属上级(包含自身)。
# 功能:反向递归查询本级及所有直属上级
# 参数说明:
# 表名:topo_tree
# 父ID字段:parent_id
# 主键ID字段:id
# 底层ID示例值:15
SELECT
T2.*
FROM
(
SELECT
@r AS _id,
( SELECT @r := parent_id FROM topo_tree WHERE id = _id ) AS parent_id,
@l := @l + 1 AS lvl
FROM
( SELECT @r := 15, @l := 0 ) vars,
topo_tree h
WHERE
@r <> 0
) T1
JOIN topo_tree T2 ON T1._id = T2.id
ORDER BY
T1.lvl DESC;