获取某个菜单的所有子孙节点同理

SELECT id,dept_name,parent_id
FROM
(SELECT id,parent_id,dept_name FROM sys_dept where parent_id > 0 ORDER BY parent_id, sort) a,
(SELECT @pid :=1) b
WHERE (FIND_IN_SET(parent_id,@pv)>0 And @pid := concat(@pid, ',', id))

获取所有父节点sql

此sql查询id=3的所有父节点sql

SELECT t2.id FROM ( SELECT @r AS _id, (SELECT @r := parent_id FROM dept WHERE id = _id) AS parent_id, @l := @l + 1 AS lvl FROM (SELECT @r := 3, @l := 0) vars, dept WHERE @r != 0) t1 JOIN dept t2 ON t1._id = t2.id ORDER BY t1.lvl DESC