MySQL通过id查询父子节点信息

表和数据

drop table department;
--部门表
CREATE TABLE IF NOT EXISTS `department`(
   `id` INT UNSIGNED AUTO_INCREMENT,
   `superior_id` VARCHAR(10) NOT NULL  COMMENT '上级ID,一级部门为0',  
   `department_name` VARCHAR(40) NOT NULL COMMENT '部门名称', 
   `remark` VARCHAR(300) COMMENT '备注', 
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO department  (superior_id, department_name, remark) 
VALUES
  (0, '一级部门', 'a'),
  (1, '二级部门', 'b'),
  (2, '三级部门', 'c'),
  (3, '四级部门', 'a'),
  (4, '五级部门', 'b');

查询父节点

SQL语句

SELECT id, superior_id, department_name, remark
    FROM ( 
        SELECT 
                @r AS _id, 
                (SELECT @r := superior_id FROM department WHERE id = _id) AS parent_id, 
                 @l := @l + 1 AS lvl 
        FROM 
                (SELECT @r := 3, @l := 0) vars, 
                department h 
        WHERE @r <> 0) T1 
    JOIN department T2 
    ON T1._id = T2.id
ORDER BY id ;

查询结果

查询子节点

SQL语句

select id from (
              select t1.id,
              if(find_in_set(superior_id, @pids) > 0, @pids := concat(@pids, ',', id), 0) as ischild
              from (
                   select id,superior_id from department t  order by superior_id, id
                  ) t1,
                  (select @pids := 3) t2
             ) t3 where ischild != 0

查询结果