表和数据
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
查询结果