SELECT T2.id, T2.name
FROM (
SELECT
@r AS _id,
(SELECT @r := parent_id FROM t_department WHERE id = _id) AS parent_id,
@l := @l + 1 AS lvl
FROM
(SELECT @r := #{id}, @l := 0) vars,
t_department h
WHERE @r <> 0) T1
JOIN t_department T2
ON T1._id = T2.id
// and dept_type=2 (加上这个返回指定部门父节点(只返回一条),不加默认查所以有的父节点)
ORDER BY T1.lvl DESC