mysql递归cte（公用表表达式）分层数据（邻接列表模型）

（一）递归公用表表达式(CTE)是一个具有引用CTE名称本身的子查询的CTE。以下说明递归CTE的语法 -

WITH RECURSIVE cte_name AS (    initial_query  -- anchor member    UNION ALL    recursive_query -- recursive member that references to the CTE name)SELECT * FROM cte_name;
SQL

递归CTE由三个主要部分组成：

• 形成CTE结构的基本结果集的初始查询(initial_query)，初始查询部分被称为锚成员。
• 递归查询部分是引用CTE名称的查询，因此称为递归成员。递归成员由一个UNION ALL或UNION DISTINCT运算符与锚成员相连。
• 终止条件是当递归成员没有返回任何行时，确保递归停止。

递归CTE的执行顺序如下：

1. 首先，将成员分为两个：锚点和递归成员。
2. 接下来，执行锚成员形成基本结果集(R0)，并使用该基本结果集进行下一次迭代。
3. 然后，将Ri结果集作为输入执行递归成员，并将Ri+1作为输出。
4. 之后，重复第三步，直到递归成员返回一个空结果集，换句话说，满足终止条件。

1. 最后，使用UNION ALL运算符将结果集从R0到Rn组合。

注意：递归成员只能在其子句中引用CTE名称，而不是引用任何子查询。

（二）使用MySQL递归CTE遍历分层数据

您可以应用递归CTE以自顶向下的方式查询整个组织结构，如下所示：

WITH RECURSIVE employee_paths AS  ( SELECT employeeNumber,           reportsTo managerNumber,           officeCode,            1 lvl   FROM employees   WHERE reportsTo IS NULL     UNION ALL     SELECT e.employeeNumber,            e.reportsTo,            e.officeCode,            lvl+1     FROM employees e     INNER JOIN employee_paths ep ON ep.employeeNumber = e.reportsTo )SELECT employeeNumber,       managerNumber,       lvl,       cityFROM employee_paths epINNER JOIN offices o USING (officeCode)ORDER BY lvl, city;
SQL

让我们将查询分解成更小的部分，使其更容易理解。
首先，使用以下查询形成锚成员：

SELECT     employeeNumber, reportsTo managerNumber, officeCodeFROM    employeesWHERE    reportsTo IS NULL
SQL

此查询(锚成员)返回reportTo为NULL的总经理。

其次，通过引用CTE名称来执行递归成员，在这个示例中为 employee_paths：

SELECT     e.employeeNumber, e.reportsTo, e.officeCodeFROM    employees e        INNER JOIN    employee_paths ep ON ep.employeeNumber = e.reportsTo
SQL

此查询(递归成员)返回经理的所有直接上级，直到没有更多的直接上级。 如果递归成员不返回直接上级，则递归停止。

第三，使用employee_paths的查询将CTE返回的结果集与offices表结合起来，以得到最终结果集合。

（三）邻接列表模型解决分层数据

在使用邻接列表模型之前，应该熟悉一些术语：

• 电子设备(Electronics)是顶级节点或根节点。
• 笔记本电脑，相机和照片，手机和配件(Laptops, Cameras & photo, Phones & Accessories)节点是Electronics节点的子节点。反之亦然Electronics节点是Laptops, Cameras & photo, Phones & Accessories节点的父节点。
• 叶子节点是没有子节点的节点，例如Laptops，PC，Android，iOS等，而非叶节点是至少有一个子节点的节点。
• 一个节点的子孙节点被称为后代节点。一个节点的父节点，祖父节点等也被称为祖先节点。

要对此类树进行建模，我们可以创建一个名为category的表，其中包含三个列：id，title和parent_id，如下所示：

CREATE TABLE category (  id int(10) unsigned NOT NULL AUTO_INCREMENT,  title varchar(255) NOT NULL,  parent_id int(10) unsigned DEFAULT NULL,  PRIMARY KEY (id),  FOREIGN KEY (parent_id) REFERENCES category (id)     ON DELETE CASCADE ON UPDATE CASCADE);
SQL

表中的每一行都是由id列标识的树中的一个节点。 parent_id列是category表本身的外键。它像一个指向id列的指针。

插入数据

树的根节点没有父节点，因此parent_id设置为NULL。其他节点必须只有一个父节点。

要插入根节点数据，请将parent_id设置为NULL，如下所示：

INSERT INTO category(title,parent_id) VALUES('Electronics',NULL);
SQL

要插入非根节点，只需要将其parent_id设置为其父节点的ID值。 例如，Laptop & PC和Cameras & Photos，以及Phone & Accessories节点的parent_id设置为1，参考以下语句：

INSERT INTO category(title,parent_id) VALUES('Laptops & PC',1);INSERT INTO category(title,parent_id) VALUES('Laptops',2);INSERT INTO category(title,parent_id) VALUES('PC',2);INSERT INTO category(title,parent_id) VALUES('Cameras & photo',1);INSERT INTO category(title,parent_id) VALUES('Camera',5);INSERT INTO category(title,parent_id) VALUES('Phones & Accessories',1);INSERT INTO category(title,parent_id) VALUES('Smartphones',7);INSERT INTO category(title,parent_id) VALUES('Android',8);INSERT INTO category(title,parent_id) VALUES('iOS',8);INSERT INTO category(title,parent_id) VALUES('Other Smartphones',8);INSERT INTO category(title,parent_id) VALUES('Batteries',7);INSERT INTO category(title,parent_id) VALUES('Headsets',7);INSERT INTO category(title,parent_id) VALUES('Screen Protectors',7);
SQL

查找根节点

根节点是没有父节点的节点。换句话说，它的parent_id为NULL：

SELECT    id, titleFROM    categoryWHERE    parent_id IS NULL;
SQL

查找节点的直接子节点

以下查询获取根节点的直接子节点，参考以下查询语句 -

SELECT    id, titleFROM    categoryWHERE    parent_id = 1;
SQL

查找叶节点

叶节点是没有子节点的节点。

SELECT    c1.id, c1.titleFROM    category c1        LEFT JOIN    category c2 ON c2.parent_id = c1.idWHERE    c2.id IS NULL;
SQL

查询整个树

以下递归公用表表达式(CTE)检索整个类别树。 请注意，自从MySQL 8.0起，CTE功能已经可用了。

WITH RECURSIVE category_path (id, title, path) AS(  SELECT id, title, title as path    FROM category    WHERE parent_id IS NULL  UNION ALL  SELECT c.id, c.title, CONCAT(cp.path, ' > ', c.title)    FROM category_path AS cp JOIN category AS c      ON cp.id = c.parent_id)SELECT * FROM category_pathORDER BY path;
SQL

查询子树

以下查询获取ID为7的Phone＆Accessories的子树。

WITH RECURSIVE category_path (id, title, path) AS(  SELECT id, title, title as path    FROM category    WHERE parent_id = 7  UNION ALL  SELECT c.id, c.title, CONCAT(cp.path, ' > ', c.title)    FROM category_path AS cp JOIN category AS c      ON cp.id = c.parent_id)SELECT * FROM category_pathORDER BY path;
SQL

得到以下结果 -

查询单个路径

要查询从下到上的单一路径，例如从iOS到Electronics，请使用以下语句：

WITH RECURSIVE category_path (id, title, parent_id) AS(  SELECT id, title, parent_id    FROM category    WHERE id = 10 -- child node  UNION ALL  SELECT c.id, c.title, c.parent_id    FROM category_path AS cp JOIN category AS c      ON cp.parent_id = c.id)SELECT * FROM category_path;
SQL

计算每个节点的级别

假设根节点的级别为0，下面的每个节点都有一个等于其父节点的级别加1的级别。

WITH RECURSIVE category_path (id, title, lvl) AS(  SELECT id, title, 0 lvl    FROM category    WHERE parent_id IS NULL  UNION ALL  SELECT c.id, c.title,cp.lvl + 1    FROM category_path AS cp JOIN category AS c      ON cp.id = c.parent_id)SELECT * FROM category_pathORDER BY lvl;
SQL

如下所示 -

删除节点及其后代

要删除节点及其后代，只需删除节点本身，则所有后代将被删除的DELETE CASCADE自动删除

例如，要Laptops & PC节点及其子节点(Laptops , PC)，请使用以下语句：

DELETE FROM category WHERE    id = 2;
SQL

删除节点并提升其后子节点

删除非叶节点并提升其后子节点：

• 首先，将节点的直接子节点的parent_id更新为新父节点的ID。
• 然后，删除节点。

例如，要删除Smartphones节点并其子项，例如Android，iOS，Other Smartphones节点：

首先，更新Smartphones的所有直接子节点项的parent_id：

UPDATE category SET     parent_id = 7 -- Phones & AccessoriesWHERE    parent_id = 5; -- Smartphones
SQL

其次，删除Smartphones节点：

DELETE FROM category WHERE    id = 8;
SQL

两个语句都应该包含在一个事务中：

BEGIN;UPDATE category SET     parent_id = 7 WHERE     parent_id = 5;DELETE FROM category WHERE     id = 8;COMMIT;
SQL

移动子树

要移动子树，只需更新子树的顶级节点的parent_id。 例如，要移动Cameras & photo作为Phone and Accessories的子节点，可使用以下语句：

UPDATE category SET     parent_id = 7WHERE    id = 5;