递归CTE基本用法（Mysql、Oracle查询树状结构的结点数据）

---

零、大数据与递归CTE

Hive不支持递归CTE，但可以通过Spark SQL遍历出带层级关系的数据。
参考链接：Spark SQL实现遍历带父子id的树状结构表数据，生成带层级关系的维表数据

一、基本用法（太长不看版）

数据库经常存在这样一种带树状结构的结点的数据集t_weight：

id	node_name	score	parent_id
1	A	11	null
2	B	22	null
3	C	33	1
4	D	44	3
5	E	55	2
6	F	66	3
7	G	77	6

如果想仅靠SQL查询出id = 1下所有子节点及相关数据（如根结点、结点路径、当前结点层级、结点路径的值汇总等）：

id	node_name	score	parent_id	root_id	node_path	node_level	total_weight
1	A	11	null	1	1	1	11
3	C	33	1	1	->1->3	2	44
4	D	44	3	1	->1->3->4	3	88
6	F	66	3	1	->1->3->6	3	110
7	G	77	6	1	->1->3->6->7	4	187

可以有以下方式：

1.1 递归CTE

在以下版本的关系型数据库可用：
Oracle 11g（据说在更老的版本已经有了，11gR2得到了增强）
Mysql 8.0
SQL Server 2005（网上有资料证实可用，未亲测）

以下列出Oracle和Mysql的例子：

Oracle语法

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

Mysql语法

with recursive t_rec as -- recursive为关键字，可以不列出字段列表
(
    select
         m.id
        ,m.node_name
        ,m.weight
        ,m.parent_id
        ,m.id as root_id
        ,concat('->',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 -- 根结点
        ,concat(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与之前的源表id关联
)
select * from t_rec

SQL Server请参考以下链接：
https://blog.csdn.net/jsjpanxiaoyu/article/details/54744677

1.2 Oracle 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 = 1
CONNECT BY t.parent_id = PRIOR t.id
-- 生成的结果集的parent_id与之前的源表id关联（prior有“之前的”的含义）

---

二、CTE与递归的基本概念

2.1 CTE（公用表表达式，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子句的效果）。

简单示例（Mysql语法）

WITH RECURSIVE cte (n) AS
(
  SELECT 1 -- 有兴趣的朋友可以将1改为NULL试试 
  UNION ALL
  SELECT n + 1 FROM cte
  WHERE n < 5
  -- 当n<5时，子查询会一直有结果输出。直到条件不满足时，终止并输出所有数据
)
-- 输出结果为5行数字，分别为1、2、3、4、5
SELECT * FROM cte;

Oracle的更简单写法示例

以上例子在Oracle有一个更简单的写法：

SELECT LEVEL AS n FROM dual CONNECT BY LEVEL <= 5

实际上还存在增加关键字来控制递归次数，达到终止递归的效果。
当递归出现循环时，也可以添加关键字，当遇到之前已经遍历过的数据时，该行数据将终止递归并被标记。
受限于篇幅，以上功能可通过搜索引擎了解。
顺带一提，Oracle称为递归子查询因子化 (Recursive Subquery Factoring，RSF)。

---

三、参考资料

MySQL CTE(公共表表达式)：https://www.yiibai.com/mysql/cte.html
MySQL CTE 官网资料：https://dev.mysql.com/doc/refman/8.0/en/with.html#common-table-expressions-recursive
递归（百度百科）：https://baike.baidu.com/item/%E9%80%92%E5%BD%92/1740695
《oracle sql 高级编程》