-- 查询自己和所有上级
SELECT
T0.*,
T1.dept_name,
T1.parent_id
FROM
(
SELECT
@r AS _id,
( SELECT @r := parent_id FROM sys_dept WHERE dept_id = _id ) AS parent_id1,
@l := @l + 1 AS lvl
FROM
( SELECT @r := '103', @l := 0 ) vars,
sys_dept h
WHERE
@r <> 0
) T0
JOIN sys_dept T1 ON T0._id = T1.dept_id
ORDER BY
dept_id;
-- 查询自己和所有下级
SELECT
DATA.*
FROM
(
SELECT
@ids AS _ids,
( SELECT @ids := GROUP_CONCAT( dept_id ) FROM sys_dept WHERE FIND_IN_SET( parent_id, @ids ) ) AS cids,
@l := @l + 1 AS lvl
FROM
sys_dept,
( SELECT @ids := '102', @l := 0 ) b
WHERE
@ids IS NOT NULL
) id,
sys_dept DATA
WHERE
FIND_IN_SET( DATA.dept_id, id._ids )
ORDER BY
lvl,
dept_id;
11-25
1206
![](https://csdnimg.cn/release/blogv2/dist/pc/img/readCountWhite.png)
07-27