一、根据叶子节点的某个id查询往上查询
SELECT d3.*
FROM (
SELECT @r AS id,
(SELECT @r := parent_id FROM department WHERE id = @r) AS tmp_parent_id
FROM (SELECT @r := 9) leafNodeId,
department hd) d2
INNER JOIN department d3 ON d2.id = d3.id AND d2.tmp_parent_id is not null
ORDER BY d3.id
1、语句说明
(1)@r := 9是叶子节点id赋值,此处可以改成普通查询赋值的方式,比如按名称查询id
SELECT @r := id from department leaf where leaf.name
= ‘后台一组一分队’
二、根据父节点id往下查询所有的子节点
SELECT d2.*
FROM (
SELECT t1.id,
IF(find_in_set(t1.parent_id, @pids) > 0, @pids := concat(@pids, ‘,’, t1.id), 0) AS leafNodeId
FROM (SELECT d1.id, d1.parent_id FROM department d1) t1,
(SELECT @pids := 5 ) t2
) t3
INNER JOIN department d2 ON d2.id = t3.id
WHERE t3.leafNodeId != 0
1、语句说明
(1)@pids := 5是父节点赋值,此处同样可以改成普通条件查询赋值
SELECT @pids := parent.id from department parent where parent.name = ‘后台组’
(2)IF(find_in_set(t1.parent_id, @pids) > 0, @pids := concat(@pids, ‘,’, t1.id), 0)
遍历表d1,判断parent_id是否在@pids中,如果在就把当前节点加入@pids中,不在就标识为0
(3)这里主要是利用了子节点的id肯定比父id大进行顺序遍历,如果发现查询结果不正确,可在d1中增加按id排序
(4)如果表较大,尽量增加一些条件过滤掉一些d1中的一些数据,比如d1.parent_id > 5等
————————————————
版权声明:本文为CSDN博主「Thomson Zhu」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/thomson2021/article/details/115107761