Mysql 刷题笔记 01.05 用CTE递归查询树状数据

-- 01.05 递归查询树状数据

CREATE TABLE T0105(
	ID INT,
	NAME VARCHAR(10),
	ParentId INT
);

INSERT INTO T0105 VALUES (1,'北京市',0);
INSERT INTO T0105 VALUES (2,'广东省',0);
INSERT INTO T0105 VALUES (3,'昌平区',1);
INSERT INTO T0105 VALUES (4,'海淀区',1);
INSERT INTO T0105 VALUES (5,'广州市',2);
INSERT INTO T0105 VALUES (6,'深圳市',2);
INSERT INTO T0105 VALUES (7,'沙河镇',3);
INSERT INTO T0105 VALUES (8,'中关村',4);
INSERT INTO T0105 VALUES (9,'天河区',5);
INSERT INTO T0105 VALUES (10,'福田区',6);

T0105
+------+-----------+----------+
| ID   | NAME      | ParentId |
+------+-----------+----------+
|    1 | 北京市    |        0 |
|    2 | 广东省    |        0 |
|    3 | 昌平区    |        1 |
|    4 | 海淀区    |        1 |
|    5 | 广州市    |        2 |
|    6 | 深圳市    |        2 |
|    7 | 沙河镇    |        3 |
|    8 | 中关村    |        4 |
|    9 | 天河区    |        5 |
|   10 | 福田区    |        6 |
+------+-----------+----------+

结果
+--------------+--------------+--------------+
| 一级地名      | 二级地名        | 三级地名     |
+--------------+--------------+--------------+
| 北京市        | 昌平区        | 沙河镇        |
| 北京市        | 海淀区        | 中关村        |
| 广东省        | 广州市        | 天河区        |
| 广东省        | 深圳市        | 福田区        |
+--------------+--------------+--------------+

mysql 8.0 也引入CTE 关键词为RECURSIVE CTE 可参考https://developer.aliyun.com/article/719994

CTE（common table expressions）是一个命名的临时结果集，仅在单个SQL语句(例如SELECT，INSERT，UPDATE或DELETE)的执行范围内存在。
与派生表类似，CTE不作为对象存储，仅在查询执行期间持续。 与派生表不同，CTE可以是自引用(递归CTE)，也可以在同一查询中多次引用。 此外，与派生表相比，CTE提供了更好的可读性和性能。

CTE与derived table最大的不同之处是
	可以自引用，递归使用（recursive cte）
	在语句级别生成独立的临时表. 多次调用只会执行一次
	一个cte可以引用另外一个cte

可以修改递归次数  SET SESSION cte_max_recursion_depth = 10; 

第一个SELECT生成CTE的初始行或多个行，并且不引用CTE名称。
第二个SELECT 通过引用其FROM子句中的CTE名称产生其他行并递归。
当此部分不产生新行时，递归结束。因此，递归CTE由一个非递归 SELECT部分和一个递归SELECT部分组成。

注意CTE后面没有 结束符
WITH RECURSIVE CTE AS
(
	SELECT ID,NAME,ParentId,1 AS Level 
  FROM T0105
  WHERE ParentId=0
  UNION ALL
  SELECT t.ID,t.NAME,t.ParentId,CTE.Level+1 AS Level
  FROM T0105 t
  JOIN CTE ON t.ParentID=CTE.ID
)

SELECT t1.name AS "一级地名",t2.name AS "二级地名",t3.name AS "三级地名"
FROM
(SELECT * FROM CTE WHERE Level=1) AS t1
INNER JOIN
(SELECT * FROM CTE WHERE Level=2) AS t2 ON t1.ID=t2.parentID
INNER JOIN
(SELECT * FROM CTE WHERE Level=3) AS t3 ON t2.ID=t3.parentID