根据父节点id，查询出当前父节点的值以及所有的子节点(包含本身)？

Question

一、初始化部门表结构、数据
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查询父节点下的所有子节的点，想修改为根据父节点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

Answer 1

比较便宜的办法是维护path，比如id:5的数据其path是1,3，然后基于path搜索，例如path like '1,%'。
包含不包含本身不重要，应该说没必要考虑。提交查询的代码肯定知道当前节点的数据 - -！