MySQL如何实现递归排序

在MySQL中，可以使用递归公用表表达式（Recursive Common Table Expressions，简称CTE）来实现递归排序。递归CTE允许我们对具有层次结构或递归关联的数据进行查询。以下是一个使用递归CTE实现递归排序的示例：

假设我们有一个部门表（departments），其中每个部门都有一个上级部门ID（parent_id）。我们希望按照层级顺序对部门进行排序，首先显示顶级部门，然后是它们的子部门，依此类推。

表结构如下：

CREATE TABLE departments (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  parent_id INT,
  FOREIGN KEY (parent_id) REFERENCES departments(id)
);

插入一些示例数据：

INSERT INTO departments (name, parent_id) VALUES ('顶级部门', NULL);
INSERT INTO departments (name, parent_id) VALUES ('部门A', 1);
INSERT INTO departments (name, parent_id) VALUES ('部门B', 1);
INSERT INTO departments (name, parent_id) VALUES ('子部门A', 2);
INSERT INTO departments (name, parent_id) VALUES ('子部门B', 2);
INSERT INTO departments (name, parent_id) VALUES ('子部门C', 3);

使用递归CTE实现递归排序：

WITH RECURSIVE department_order AS (
  -- 基本情况：只包含顶级部门
  SELECT id, name, parent_id, 1 AS level
  FROM departments
  WHERE parent_id IS NULL

  UNION ALL

  -- 递归情况：包含子部门
  SELECT d.id, d.name, d.parent_id, dept_order.level + 1
  FROM departments d
  INNER JOIN department_order dept_order ON d.parent_id = dept_order.id
)
SELECT * FROM department_order ORDER BY level, id;

这个查询首先使用递归CTE创建一个名为department_order的临时表，包含所有部门及其层级。然后，我们从这个临时表中选择所有记录，并按照层级（level）和ID（id）进行排序。这将返回按层级顺序排列的部门列表。