数据准备:
表结构:
CREATE TABLE `tb_dept` (
`id` bigint(20) NOT NULL,
`parent_id` bigint(20) DEFAULT NULL,
`dept_code` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
`dept_name` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_parent_id` (`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='部门表';
插入测试数据:
INSERT INTO `test`.`tb_dept`(`id`, `parent_id`, `dept_code`, `dept_name`) VALUES (1, NULL, '01', '葫芦科技');
INSERT INTO `test`.`tb_dept`(`id`, `parent_id`, `dept_code`, `dept_name`) VALUES (2, 1, '001', '运营部');
INSERT INTO `test`.`tb_dept`(`id`, `parent_id`, `dept_code`, `dept_name`) VALUES (3, 1, '002', '技术部');
INSERT INTO `test`.`tb_dept`(`id`, `parent_id`, `dept_code`, `dept_name`) VALUES (4, 1, '003', '产品部');
INSERT INTO `test`.`tb_dept`(`id`, `parent_id`, `dept_code`, `dept_name`) VALUES (5, 1, '004', '设计部');
INSERT INTO `test`.`tb_dept`(`id`, `parent_id`, `dept_code`, `dept_name`) VALUES (6, 2, '00101', '运营一部');
INSERT INTO `test`.`tb_dept`(`id`, `parent_id`, `dept_code`, `dept_name`) VALUES (7, 2, '00102', '运营二部');
INSERT INTO `test`.`tb_dept`(`id`, `parent_id`, `dept_code`, `dept_name`) VALUES (8, 3, '00201', '技术一部');
INSERT INTO `test`.`tb_dept`(`id`, `parent_id`, `dept_code`, `dept_name`) VALUES (9, 3, '00202', '技术二部');
INSERT INTO `test`.`tb_dept`(`id`, `parent_id`, `dept_code`, `dept_name`) VALUES (10, 3, '00203', '技术三部');
INSERT INTO `test`.`tb_dept`(`id`, `parent_id`, `dept_code`, `dept_name`) VALUES (11, 3, '00204', '技术四部');
INSERT INTO `test`.`tb_dept`(`id`, `parent_id`, `dept_code`, `dept_name`) VALUES (12, 4, '00301', '运营一部');
INSERT INTO `test`.`tb_dept`(`id`, `parent_id`, `dept_code`, `dept_name`) VALUES (13, 4, '00302', '运营二部');
INSERT INTO `test`.`tb_dept`(`id`, `parent_id`, `dept_code`, `dept_name`) VALUES (14, 4, '00303', '运营三部');
INSERT INTO `test`.`tb_dept`(`id`, `parent_id`, `dept_code`, `dept_name`) VALUES (15, 5, '00401', '设计一部');
INSERT INTO `test`.`tb_dept`(`id`, `parent_id`, `dept_code`, `dept_name`) VALUES (16, 8, '0020101', '技术一部一组');
INSERT INTO `test`.`tb_dept`(`id`, `parent_id`, `dept_code`, `dept_name`) VALUES (17, 8, '0020102', '技术一部二组');
INSERT INTO `test`.`tb_dept`(`id`, `parent_id`, `dept_code`, `dept_name`) VALUES (18, 9, '0020201', '技术二部一组');
INSERT INTO `test`.`tb_dept`(`id`, `parent_id`, `dept_code`, `dept_name`) VALUES (19, 10, '0020301', '技术三部一组');
数据信息:
一、递归查询某个部门的所有子部门:
SELECT * FROM (SELECT id, parent_id, dept_code, dept_name FROM tb_dept WHERE parent_id IS NOT NULL) rd,(SELECT @pid := 3) pd
where FIND_IN_SET( parent_id, @pid ) > 0 AND @pid := concat( @pid, ',', id );
@pid := 3 初始赋值,即你需要查询其子部门的某个部门
运行结果:
二、查询某个部门的所有父部门:
SELECT
tdd.id,tdd.dept_code,tdd.dept_name
FROM
(
SELECT
( SELECT @cid := parent_id FROM tb_dept WHERE id = @cid ) AS pid
FROM
( SELECT @cid := 19 ) cs,
tb_dept td
) tp
LEFT JOIN tb_dept tdd ON tdd.id = tp.pid
WHERE
tp.pid IS NOT NULL
@cid := 19 初始赋值,即你需要查询其父部门的某个部门
运行结果:
mysql中 :=和=的区别: https://blog.csdn.net/HXNLYW/article/details/94442220