mysql 一句查所有上级或下级

那个递归消耗可以无视了。

查所有上级

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

http://www.javashuo.com/article/p-wxfqlvie-gt.html

https://zhuanlan.zhihu.com/p/452490607

点赞

发表评论

电子邮件地址不会被公开。必填项已用 * 标注