本文核心知识点
SQL 中的递归 CTE，WITH RECURSIVE 。
不废话，有兴趣就直接跳到文末【参考文献】部分，中间内容为我的练习过程。
前言
复盘（马后炮）一下，给自己以后写复杂SQL时留点案例。
以下案例均为胡说八道，会有逻辑漏洞，如有雷同皆为巧合~
以下解法仅考虑实现，未涉及性能~
信息
数据库版本
Mysql 8.0.31
数据表结构
person 族人表 id 主键 name 姓名
consanguinity 血缘表 id 主键 this_id 来源id source_id 指向id name 血缘名称
现有数据
person 表
| id | name |
|---|---|
| 1 | 张三 |
| 2 | 张三三 |
| 3 | 张重三 |
| 4 | 王二 |
| 5 | 王三 |
| 6 | 王小妹 |
consanguinity 表
| id | this_id | source_id | name |
|---|---|---|---|
| 1 | 2 | 1 | 父亲 |
| 2 | 3 | 2 | 父亲 |
| 3 | 4 | 1 | 舅父 |
| 4 | 5 | 4 | 父亲 |
| 5 | 6 | 5 | 大伯 |
需求
查询某个人的所有后代。
输出示例
例如 查询人为【张三】。
| id | name |
|---|---|
| 2 | 张三三 |
| 3 | 张重三 |
| 4 | 王二 |
| 5 | 王三 |
| 6 | 王小妹 |
SQL
WITH RECURSIVE descendants AS (
SELECT p.id, p.name
FROM person p
Left JOIN consanguinity c ON c.this_id = p.id
WHERE c.source_id = 1
UNION ALL
SELECT p.id, p.name
FROM descendants d
JOIN consanguinity c ON c.source_id = d.id
JOIN person p ON c.this_id = p.id
)
SELECT DISTINCT *
FROM descendants;
SQL解析
WITH RECURSIVE descendants AS (
-- Recursive term
-- 递归术语
SELECT p.id, p.name
FROM person p
LEFT JOIN consanguinity c ON c.this_id = p.id
WHERE c.source_id = 1
UNION ALL
-- Recursive part
-- 递归部分
SELECT p.id, p.name
FROM descendants d
JOIN consanguinity c ON c.source_id = d.id
JOIN person p ON c.this_id = p.id
)
WITH RECURSIVE 是SQL中用于定义递归公共表达式（CTE）的关键字。递归公共表达式允许在查询中递归引用自身，从而处理层次结构或递归关系的数据。
在这个例子中，WITH RECURSIVE 用于定义名为 descendants 的递归公共表达式。
递归术语（Recursive term）：定义起始查询部分，它基于 person 和 consanguinity 表，选择与指定 source_id 匹配的记录（这里是1,也就是张三的ID），并返回对应的 id 和 name。
递归部分（Recursive part）：基于之前递归结果集 descendants，通过与 consanguinity 和 person 表的连接，选择与之前结果集中的 id 匹配的记录，并返回对应的 id 和 name。这个部分的目的是递归地获取后代的信息。
可以在递归公共表达式之后的主查询中引用 descendants 表，并执行进一步的操作。
递归公共表达式是对递归查询的一种结构化方式，它使得处理层次结构数据和树型数据变得更加方便。在使用 WITH RECURSIVE 时需要小心，确保设置递归终止条件，以避免无限递归和性能问题。
新学到的知识
WITH RECURSIVE 是 SQL 语言中用于定义递归公共表达式（CTE）的关键字。递归公共表达式允许在查询中进行递归引用，从而处理层次结构、递归关系或链式关系的数据。
基本语法结构
WITH RECURSIVE 的基本语法结构如下：
WITH RECURSIVE cte_name (column1, column2, ...) AS (
-- 初始查询部分
SELECT initial_columns
FROM initial_table
WHERE condition
UNION [ALL]
-- 递归查询部分
SELECT recursive_columns
FROM cte_name
JOIN recursive_table ON join_condition
WHERE recursive_condition
)
SELECT final_query_columns
FROM cte_name
对于 WITH RECURSIVE 的用法，我们可以了解以下几点：
cte_name：递归公共表达式的名称，可以在主查询中引用这个名称。
column1, column2, ...：定义递归结果集的列名。
initial_query：初始查询部分，用于获取递归的起点数据。
initial_table：初始查询部分所关联的表。
condition：用于过滤 initial_table 中的记录。
UNION [ALL]：指定递归部分与初始查询部分的关系，ALL 表示保留所有重复的记录，而不仅仅是不重复的记录。
recursive_query：递归查询部分，用于根据已经计算的结果进行进一步的递归操作。
recursive_table：递归查询部分所关联的表。
join_condition：用于连接递归结果集和递归表之间的关联条件。
recursive_condition：用于过滤递归查询部分所关联表的记录，以控制递归的终止条件。
final_query：最终查询部分，用于从递归结果集中选择所需的列进行进一步的分析或操作。
参考文献
[1] . WITH (Common Table Expressions) . MySQL官方文档 . https://dev.mysql.com/doc/refman/8.0/en/with.html
[1] . Queries (Common Table Expressions)WITH . postgresql官方文档 . https://www.postgresql.org/docs/current/queries-with.html
[3] . Recursive SQL Expression Visually Explained . Denis Lukichev . https://builtin.com/data-science/recursive-sql
[4] . The WITH Clause . sqlite官方文档 . https://www.sqlite.org/lang_with.html
[5] . What Is a Recursive CTE in SQL? . Tihomir Babic . https://learnsql.com/blog/sql-recursive-cte/
[6] . with recursive实现导航栏 . 孤鸿君 . CSDN . https://blog.csdn.net/weixin_42345223/article/details/119534624
[7] . SQL中with recursive用法案例详解 . Lee Howard . CSDN . https://blog.csdn.net/pilihaotian/article/details/128313615
