MySQL-根据部门id查询本级和上级部门

表设计：

CREATE TABLE `t_acs_dic_dept` (
  `dept_id` decimal(10,0) NOT NULL,
  `dept_name` varchar(100) DEFAULT NULL,
  `parent_id` decimal(10,0) DEFAULT NULL COMMENT '上级部门',
  `path` varchar(256) DEFAULT NULL COMMENT '路径',
  `status` decimal(1,0) DEFAULT NULL COMMENT '0:未删除，1已删除',
  `qx` int(1) NOT NULL COMMENT '0:复制，1不复制',
  `remake2` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`dept_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

sql:

​
SELECT  t2.dept_id
		FROM (
				 SELECT
					 @r AS _id,
		(SELECT @r := parent_id FROM t_acs_dic_dept WHERE dept_id = _id) AS parent_id,
		@l := @l - 1 AS le
				 FROM
					 (SELECT @r := 9, @l := 9) vars,
					 t_acs_dic_dept h
				 WHERE @r  > 0) t1
				 JOIN t_acs_dic_dept t2
					  ON t1._id = t2.dept_id
		ORDER BY t1.le asc;