1.数据准备

CREATE TABLE departments (
  id INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  parent_id INT
);


INSERT INTO departments (id, name, parent_id) VALUES
(1, '总公司', NULL),
(2, '研发部', 1),
(3, '市场部', 1),
(4, '产品部', 2),
(5, '测试部', 2),
(6, '销售部', 3),
(7, '客服部', 3),
(8, '市场推广部', 6),
(9, '市场调研部', 6),
(10, '售后服务部', 7);

2.我们想要查询出所有的部门以及它们的层级关系。可以使用以下的递归查询语句

WITH RECURSIVE cte AS (
  SELECT id, name, parent_id, 0 as level
  FROM departments
  WHERE parent_id IS NULL
  UNION ALL
  SELECT d.id, d.name, d.parent_id, c.level + 1
  FROM departments d
  INNER JOIN cte c ON d.parent_id = c.id
)
SELECT id, name, parent_id, level
FROM cte
ORDER BY id;

上述代码中，我们使用了WITH RECURSIVE关键字来定义一个递归公用表达式，该表达式逐层递归查询每个部门的信息，直到查询到根部门。具体步骤如下：

1. 从顶层部门开始查询（即parent_id为NULL的部门），并将查询结果存储到cte表中。
2. 对于每个查询结果，我们再次查询其子部门，并将查询结果与上一级部门的id相连接，以获取它们的上下级关系。这一过程持续进行，直到查询到最底层的子部门。
3. 最终，我们得到一个包含所有部门信息的递归查询结果，每一行表示一个部门的信息及其在整个部门层级结构中的层数
4. 最终结果如下图：

 3.sql详细步骤解析

三次递归结果union all 得到最终结果