前言：在许多场景中需要直接加载一颗树或者某个树的一部分，多层级数据情况下，如果单用数据库语句的情况下，需要用到CTE递归语句。
一、CTE语法（ORACLE、MySQL大同小异）：
WITH cte_name (column_list) AS (
query
)
SELECT * FROM cte_name;
注意：
请注意，查询中的列数必须与column_list中的列数相同。如果省略column_list，CTE将使用定义CTE的查询的列列表。
二、CTE与递归的基本概念
2.1CTE（公用表表达式，Common Table Expression）
CTE是一个命名的临时结果集，仅在单个SQL语句（例如SELECT，INSERT，UPDATE或DELETE）的执行范围内存在。
与派生表（子查询）类似，CTE不作为对象存储，仅在查询执行期间持续。与派生表不同，CTE可以是自引用（递归CTE），也可以在同一查询中多次引用。此外，与派生表相比，CTE提供了更好的可读性和性能。
在Oracle有些许不同，这种功能被称为“子查询因子化”（Subquery Factoring）。
示例：
WITH customers_in_usa AS ( SELECT
customerName, state FROM
customers WHERE
country = 'USA'
) SELECT
customerName FROM
customers_in_usa -- 第1次引用WHERE
state = 'CA' UNION ALL SELECT
customerName FROM
customers_in_usa -- 第2次引用WHERE
state = 'LA'
2.2递归
程序调用自身的编程技巧称为递归（ recursion）。上面的子查询中出现自身的别名就是一个例子。一般来说，递归需要有边界条件（终止条件）、递归前进段（执行逻辑）和递归返回段（终止执行并返回结果）。当边界条件不满足时，递归前进（继续执行）；当边界条件满足时，递归返回（终止执行并返回结果）。
2.3递归CTE
综上所述，递归CTE是不断调用自己，直到满足终止条件才输出所有数据。终止条件一般是“某一次查询的结果集没有数据”（类似于exists子句的效果）。
三、实现示例：
3.1MySQL CTE实现树查找表结构：
CREATE TABLE `t_sys_organization` (
`org_id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '组织机构Id',
`org_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '组织机构名称',
`parent_id` int(11) unsigned NOT NULL COMMENT '父节点为空的是根机构',
`level` int(11) DEFAULT NULL COMMENT '组织层级',
`is_valid` int(11) DEFAULT NULL COMMENT '是否有效', PRIMARY KEY (`org_id`),
UNIQUE KEY `idx_oname` (`org_name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=84 DEFAULT CHARSET=utf8
--实现查找根节点下面所有子节点并带着部门名称
with recursive t_rec as -- recursive为关键字，可以不列出字段列表(
select
m.org_id
,m.org_name
,m.parent_id
,m.level
,concat('->',m.org_id) as nodepath -- 拼接各结点的id
,concat('->',m.org_name) as nodename from t_sys_organization m
where org_id = 1 union all
select
t1.org_id
,t1.org_name
,t1.parent_id
,t1.level
,concat(nodepath,'->',t1.org_id) -- 拼接各结点的id
,concat(nodename,'->',t1.org_name) -- 拼接各结点的id from t_sys_organization t1
join t_rec t2
on t1.parent_id = t2.org_id
-- 递归生成的结果集t2的parent_id与之前的源表id关联
)
select * from t_rec
3.2ORACLE实现 ：cte方式
WITH t_rec( id,node_name,weight,parent_id
,root_id,node_path,node_level,total_weight
) AS -- 递归子查询必须列出字段列表(SELECT m.id
,m.node_name
,m.weight
,m.parent_id
,m.id AS root_id
,'->' || m.id AS node_path
,1 AS node_level
,m.weight AS total_weight FROM t_weight m
WHERE id = 1 UNION ALL SELECT t1.id
,t1.node_name
,t1.weight
,t1.parent_id
,t2.root_id -- 根结点
,t2.node_path || '->' || t1.id -- 拼接各结点的id
,t2.node_level + 1 -- 结点层级
,t2.total_weight + t1.weight -- 将根结点到当前结点的路径上各结点的值汇总FROM t_weight t1
JOIN t_rec t2
ON t1.parent_id = t2.id -- 递归生成的结果集t2的parent_id与之前的源表t1的id关联
)
SELECT * FROM t_rec
3.3ORACLE 实现方式：start with connect by
ORACLE 自带树查找语法：通过根节点找子节点：SELECT t.id
,t.node_name
,t.weight
,t.parent_id
,connect_by_root(t.id) AS root_id -- 根结点
,sys_connect_by_path(t.id, '->') AS node_path -- 拼接各结点的id
,LEVEL AS node_level -- 结点层级
,NULL AS total_weight -- 暂时无法实现累加操作FROM t_weight t
START WITH t.id = 10101
CONNECT BY prior t.parent_id = t.id
ORACLE 自带树查找语法：通过叶子节点找根节点：SELECT t.id
,t.node_name
,t.weight
,t.parent_id
,connect_by_root(t.id) AS root_id -- 根结点
,sys_connect_by_path(t.id, '->') AS node_path -- 拼接各结点的id
,LEVEL AS node_level -- 结点层级
,NULL AS total_weight -- 暂时无法实现累加操作FROM t_weight t
START WITH t.id = 1
CONNECT BY t.parent_id = PRIOR t.id
3.4其他示例：
从根节点开始查询：
Select t.*,level from table_name t start with t.id=0 connect by prior t.id=t.pid order by level
从子节点开始查询：
把上面的开始节点换成开始的子节点，并且prior 放在子节点前面
Select t.*,level from table_name t start with t.id=10 connect by t.id= prior t.pid order by level
eg2：
table:
CREATE TABLE `region` (
`id` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
`pid` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
`deep` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
`name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
`pinyin_prefix` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
`pinyin` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
`ext_id` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
`ext_name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL, KEY `idx_region_pid` (`pid`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
-- 查找所有叶子节点explain with recursive r as (
select id,name from region c where id=530403 union all
select c.id,CONCAT(r.name, '>', c.name) as name from region c,r where r.id = c.pid
)
select id,name from r;
-- 查找所有父节点
with recursive r as (
select id,pid,name from region c where id=110118115 union all
select c.id,c.pid,CONCAT(r.name, '>', c.name) as name from region c,r where c.id = r.pid
)
select pid,name from r;
select * from region
ALTER TABLE `demo`.`region`
ADD INDEX `idx_region_pid`(`pid`) USING BTREE; select * from region
