那个递归消耗可以无视了。
查所有上级
SELECT T2.`id` FROM ( SELECT @r AS _id, (SELECT @r := father FROM `user` WHERE `id` = _id) AS parent_id, @l := @l + 1 AS lvl FROM (SELECT @r := {$myid}, @l := 0) vars, `user` h WHERE @r <> 0 AND father > 0 ) T1 JOIN `user` T2 ON T1._id = T2.`id` ORDER BY T1.lvl DESC
查所有下级,不包自己
SELECT `id` FROM
(
SELECT * FROM `user` where father > 0 ORDER BY father, `id` DESC
) realname_sorted,
(SELECT @pv :={$myid}) initialisation
WHERE (FIND_IN_SET(father,@pv)>0 And @pv := concat(@pv, ',', `id`))
`user` 改成你的表
`id` 改成你的id索引列名
`father` 改成你的父级列名
{$myid} 你要查询的ID的条件
参考:
https://segmentfault.com/a/1190000007531328