温馨提示×

在复杂查询中使用MySQL递归排序的方法

小樊
82
2024-10-02 09:38:11
栏目: 云计算

在MySQL中,可以使用递归公用表表达式(Recursive Common Table Expressions,简称CTE)来实现复杂查询中的递归排序

以下是一个使用MySQL递归排序的示例:

假设我们有一个组织结构表(organization),结构如下:

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

表中数据如下:

INSERT INTO organization (name, parent_id) VALUES ('CEO', NULL);
INSERT INTO organization (name, parent_id) VALUES ('CTO', 1);
INSERT INTO organization (name, parent_id) VALUES ('CFO', 1);
INSERT INTO organization (name, parent_id) VALUES ('Dev Team', 2);
INSERT INTO organization (name, parent_id) VALUES ('HR Team', 2);
INSERT INTO organization (name, parent_id) VALUES ('Finance Team', 3);

现在,我们希望按照层级对组织结构进行排序,同一层级的成员按名称升序排列。可以使用以下递归查询实现:

WITH RECURSIVE org_hierarchy (id, name, parent_id, level, sort_order) AS (
  SELECT id, name, parent_id, 1, COALESCE(parent_id, id)
  FROM organization
  WHERE parent_id IS NULL
  UNION ALL
  SELECT o.id, o.name, o.parent_id, oh.level + 1, COALESCE(o.parent_id, oh.sort_order)
  FROM organization o
  INNER JOIN org_hierarchy oh ON o.parent_id = oh.id
)
SELECT id, name, parent_id, level, sort_order
FROM org_hierarchy
ORDER BY level, sort_order;

查询结果如下:

+----+------------------+-----------+-------+-------------+
| id | name             | parent_id | level | sort_order  |
+----+------------------+-----------+-------+-------------+
|  1 | CEO              |      NULL |     1 |           1 |
|  2 | CTO              |         1 |     2 |           2 |
|  3 | CFO              |         1 |     2 |           3 |
|  4 | Dev Team         |         2 |     3 |           1 |
|  5 | HR Team          |         2 |     3 |           2 |
|  6 | Finance Team     |         3 |     3 |           1 |
+----+------------------+-----------+-------+-------------+

在这个示例中,我们首先使用递归CTE创建了一个名为org_hierarchy的临时表,其中包含了每个组织成员的ID、名称、父ID、层级和排序顺序。然后,我们对这个临时表进行排序,先按层级排序,再按排序顺序排序。最后,我们得到了按照层级和名称升序排列的组织结构。

0