【代码备忘录】SQL 中的递归写法案例

本文核心知识点

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