mysql根据父节点查询所有的子节点以及根据子节点往上查询所有父节点（普通sql语句查询）

一、初始化部门表结构、数据

1、部门表结构

CREATE TABLE `department` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `name` varchar(255) NOT NULL COMMENT ' 部门名称',
  `parent_id` int NOT NULL COMMENT '父id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

2、初始化数据

INSERT INTO `department`(`id`, `name`, `parent_id`) VALUES (1, '技术部', 0);
INSERT INTO `department`(`id`, `name`, `parent_id`) VALUES (2, '前端组', 1);
INSERT INTO `department`(`id`, `name`, `parent_id`) VALUES (3, '前端一组', 2);
INSERT INTO `department`(`id`, `name`, `parent_id`) VALUES (4, '前端二组', 2);
INSERT INTO `department`(`id`, `name`, `parent_id`) VALUES (5, '后台组', 1);
INSERT INTO `department`(`id`, `name`, `parent_id`) VALUES (6, '后台一组', 5);
INSERT INTO `department`(`id`, `name`, `parent_id`) VALUES (7, '后台二组', 5);
INSERT INTO `department`(`id`, `name`, `parent_id`) VALUES (8, '后台三组', 5);
INSERT INTO `department`(`id`, `name`, `parent_id`) VALUES (9, '后台一组一分队', 6);
INSERT INTO `department`(`id`, `name`, `parent_id`) VALUES (10, '后台一组二分队', 6);
INSERT INTO `department`(`id`, `name`, `parent_id`) VALUES (11, '测试组', 1);
INSERT INTO `department`(`id`, `name`, `parent_id`) VALUES (12, '测试一组', 11);

二、根据叶子节点的某个id查询往上查询

SELECT d3.*
    FROM ( 
        SELECT @r AS id, 
          (SELECT @r := parent_id FROM department WHERE id = @r) AS tmp_parent_id
        FROM (SELECT @r := 9) leafNodeId, 
             department hd) d2 
    INNER JOIN department d3 ON d2.id = d3.id AND d2.tmp_parent_id is not null
ORDER BY d3.id

1、语句说明

（1）@r := 9是叶子节点id赋值，此处可以改成普通查询赋值的方式，比如按名称查询id

SELECT @r := id from department leaf where leaf.`name` = '后台一组一分队'

三、根据父节点id往下查询所有的子节点

SELECT d2.* 
	FROM (
         SELECT t1.id,
                 IF(find_in_set(t1.parent_id, @pids) > 0, @pids := concat(@pids, ',', t1.id), 0) AS leafNodeId
         FROM (SELECT d1.id, d1.parent_id FROM department d1) t1,
		  (SELECT @pids := 5 ) t2 
         ) t3 
         INNER JOIN department d2 ON d2.id = t3.id
WHERE t3.leafNodeId != 0

1、语句说明

（1）@pids := 5是父节点赋值，此处同样可以改成普通条件查询赋值

SELECT @pids := parent.id from department parent where parent.name = '后台组'

（2）IF(find_in_set(t1.parent_id, @pids) > 0, @pids := concat(@pids, ‘,’, t1.id), 0)
遍历表d1，判断parent_id是否在@pids中，如果在就把当前节点加入@pids中，不在就标识为0
（3）这里主要是利用了子节点的id肯定比父id大进行顺序遍历，如果发现查询结果不正确，可在d1中增加按id排序
（4）如果表较大，尽量增加一些条件过滤掉一些d1中的一些数据，比如d1.parent_id > 5等