溫馨提示×

在復(fù)雜查詢中使用MySQL遞歸排序的方法

小樊
81
2024-10-02 09:38:11
欄目: 云計算

在MySQL中，可以使用遞歸公用表表達式（Recursive Common Table Expressions，簡稱CTE）來實現(xiàn)復(fù)雜查詢中的遞歸排序

以下是一個使用MySQL遞歸排序的示例：

假設(shè)我們有一個組織結(jié)構(gòu)表（organization），結(jié)構(gòu)如下：

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)
);

表中數(shù)據(jù)如下：

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);

現(xiàn)在，我們希望按照層級對組織結(jié)構(gòu)進行排序，同一層級的成員按名稱升序排列?？梢允褂靡韵逻f歸查詢實現(xiàn)：

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;

查詢結(jié)果如下：

+----+------------------+-----------+-------+-------------+
| 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創(chuàng)建了一個名為org_hierarchy的臨時表，其中包含了每個組織成員的ID、名稱、父ID、層級和排序順序。然后，我們對這個臨時表進行排序，先按層級排序，再按排序順序排序。最后，我們得到了按照層級和名稱升序排列的組織結(jié)構(gòu)。

0 踩