mysql 8+ 从子级找到上面10级父级，每一级的父级都要查询的案例

1、创建表和测试数据

DROP TABLE IF EXISTS `depts`;
CREATE TABLE `depts`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `parent_id` int(0) NULL DEFAULT 0,
  `level` int(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of depts
-- ----------------------------
INSERT INTO `depts` VALUES (1, '部门1', 0, 1);
INSERT INTO `depts` VALUES (2, '部门2', 1, 2);
INSERT INTO `depts` VALUES (3, '部门3', 2, 3);
INSERT INTO `depts` VALUES (4, '部门4', 3, 4);
INSERT INTO `depts` VALUES (5, '部门5', 4, 5);
INSERT INTO `depts` VALUES (6, '部门6', 5, 6);
INSERT INTO `depts` VALUES (7, '部门7', 6, 7);
INSERT INTO `depts` VALUES (8, '部门8', 7, 8);
INSERT INTO `depts` VALUES (9, '部门9', 8, 9);
INSERT INTO `depts` VALUES (10, '部门10', 9, 10);
INSERT INTO `depts` VALUES (11, '部门11', 10, 11);
INSERT INTO `depts` VALUES (12, '部门12', 11, 12);

2、查询id=10的10级内的父级数据

WITH RECURSIVE parent_hierarchy AS (
  -- 锚点查询：指定子级节点（这里以ID=7的"开发人员张三"为起点）
  SELECT 
    id, 
    name, 
    parent_id, 
    0 AS depth,  -- 子级自身深度为0
    CAST(name AS CHAR(1000)) AS hierarchy_chain  -- 层级链条文字描述
  FROM depts
  WHERE id = 10  -- 子级ID
  
  UNION ALL
  
  -- 递归查询：向上查找每一级父级，最多10级
  SELECT 
    p.id, 
    p.name, 
    p.parent_id, 
    ph.depth + 1 AS depth,  -- 深度递增（每上一级+1）
    -- 拼接层级链条（父级在前，子级在后）
    CONCAT(p.name, ' -> ', ph.hierarchy_chain) AS hierarchy_chain
  FROM depts p
  INNER JOIN parent_hierarchy ph 
    ON p.id = ph.parent_id  -- 关联父级ID
  WHERE ph.depth < 10  -- 限制最多向上查询10级
)
SELECT 
  depth AS "层级深度",  -- 0=子级本身，1=直接父级，2=祖父级...
  id AS "节点ID",
  name AS "节点名称",
  parent_id AS "父级ID",
  hierarchy_chain AS "层级链条"  -- 直观展示从顶级到当前子级的路径
FROM parent_hierarchy 
ORDER BY depth;  -- 按层级深度排序（从子级到顶级）

效果如下：

3、sql整改，不需要层级链条，如下：

WITH RECURSIVE parent_hierarchy AS (
  -- 锚点查询：指定子级节点（这里以ID=7的"开发人员张三"为起点）
  SELECT 
    id, 
    name, 
    parent_id, 
    0 AS depth  -- 子级自身深度为0
  FROM depts
  WHERE id = 10  -- 子级ID
  
  UNION ALL
  
  -- 递归查询：向上查找每一级父级，最多10级
  SELECT 
    p.id, 
    p.name, 
    p.parent_id, 
    ph.depth + 1 AS depth  -- 深度递增（每上一级+1）
    -- 拼接层级链条（父级在前，子级在后）

  FROM depts p
  INNER JOIN parent_hierarchy ph 
    ON p.id = ph.parent_id  -- 关联父级ID
  WHERE ph.depth < 10  -- 限制最多向上查询10级
)
SELECT 
  depth AS "层级深度",  -- 0=子级本身，1=直接父级，2=祖父级...
  id AS "节点ID",
  name AS "节点名称",
  parent_id AS "父级ID"
FROM parent_hierarchy 
ORDER BY depth;  -- 按层级深度排序（从子级到顶级）

 4、找到所有子级的数据

WITH RECURSIVE department_hierarchy AS (
    -- 初始查询：找到起始部门
    SELECT id, name, parent_id, 0 AS level
    FROM depts
    WHERE id = 2 -- 这里替换为你要查询的部门ID
    
    UNION ALL
    
    -- 递归查询：找到所有子部门
    SELECT d.id, d.name, d.parent_id, dh.level + 1 AS level
    FROM depts d
    INNER JOIN department_hierarchy dh ON d.parent_id = dh.id
)
-- 最终查询结果
SELECT id, name, parent_id, level
FROM department_hierarchy
ORDER BY level, id;