【Mysql】一篇学会SQL中的递归的用法

yqyn

已于 2023-03-29 11:58:07 修改

阅读量9.8k

点赞数 27

分类专栏： MySQL系列文章标签： sql mysql 数据库

于 2023-03-27 15:49:15 首次发布

本文链接：https://blog.csdn.net/yqyn6/article/details/129795916

版权

MySQL系列专栏收录该内容

6 篇文章 0 订阅

订阅专栏

【Mysql】SQL高级技巧——递归用法及案例详解

1. SQL递归概念:

SQL递归查询是一种用于处理具有层次结构的数据的技术。它使用递归函数来遍历树形结构，例如组织结构、分类结构等等。

递归查询通常使用 " WITH RECURSIVE " 语句实现。

WITH RECURSIVE 语句包含两部分：
a.递归部分: 定义了如何递归查询数据;
b.终止条件部分: 定义了递归查询何时停止。

2. SQL递归一般形式：

WITH RECURSIVE recursive_query_name (col1, col2, ..., coln) AS (
    -- 递归部分
    SELECT 
			initial_query_result_col1, 
			initial_query_result_col2, 
			...,
			initial_query_result_coln
    FROM initial_query
    UNION ALL
    SELECT 
			recursive_query_result_col1,
			recursive_query_result_col2, 
			..., 
			recursive_query_result_coln
    FROM recursive_query_name, recursive_query
    WHERE recursive_query_condition
)
-- 终止条件部分
SELECT * FROM recursive_query_name WHERE termination_condition;

在递归部分，我们先通过一个初始查询(initial_query)得到一些初始的结果。然后我们通过UNION ALL运算将初始结果集合并到递归查询结果中。接下来，在每次递归查询中，我们使用前一次递归的结果(recursive_query_name)与递归查询(recursive_query)进行运算，并使用WHERE条件过滤掉不需要的数据。最后，在终止条件部分中，我们使用一个条件来判断递归查询何时停止。当递归查询到终止条件时，递归查询结束，最终结果被返回。

3. SQL递归优缺点：

优点：

灵活性：SQL递归查询适用于各种类型的树形结构，而且可以根据具体的需要自定义递归查询算法。
可读性：递归查询通常比使用嵌套查询或连接查询更易于阅读和理解。它可以用简单的SQL语句来表示一个复杂的树形结构。
便于维护：SQL递归查询通常比其他方法更易于维护。例如，如果要更改树形结构中的某些节点，只需更改递归查询算法即可。

缺点：

性能：SQL递归查询通常比其他方法慢。这是因为它需要进行多次递归函数调用，并且可能需要访问大量的数据。如果不正确地编写递归查询算法，还可能会导致死循环等问题，从而影响性能。
复杂性：递归查询算法通常比其他方法更复杂。如果不熟悉递归算法，编写正确的递归查询算法可能很困难。
可伸缩性：SQL递归查询不适合处理大型数据集。当数据集变得太大时，查询可能会变得非常缓慢，甚至无法运行。

总体而言，SQL递归查询是一种非常有用的技术，可以处理树形结构的数据。虽然它具有一些缺点，但在正确使用的情况下，它仍然是一种非常强大和灵活的工具。

4.案例：公司部门关系递归查询

a.按DDL建表：

CREATE TABLE company_department (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50),
    parent_department_id INT REFERENCES company_department(department_id)
);

b.插入数据：

INSERT INTO company_department 
    (department_id, department_name, parent_department_id)
VALUES
    (1, '公司', NULL),
    (2, '人力资源部', 1),
    (3, '财务部', 1),
    (4, '市场部', 1),
    (5, '技术部', 1),
    (6, '招聘部', 2),
    (7, '薪资部', 2),
    (8, '成本控制部', 3),
    (9, '收支管理部', 3),
    (10, '品牌推广部', 4),
    (11, '销售部', 4),
    (12, '前端开发部', 5),
    (13, '后端开发部', 5)

c.递归查询公司部门关系SQL语句

WITH RECURSIVE department_tree (department_id, department_name, parent_department_id, depth, path) AS (
		SELECT 
			department_id, 
			department_name, 
			parent_department_id, 
			1 AS depth, 
			CAST(department_id AS CHAR(200)) AS path
		FROM company_department
		WHERE parent_department_id IS NULL
		UNION ALL
		SELECT 
			cd.department_id, 
			cd.department_name, 
			cd.parent_department_id, 
			dt.depth + 1 AS depth, 
			CONCAT(dt.path, ',', cd.department_id) AS path
		FROM company_department cd
			JOIN department_tree dt ON cd.parent_department_id = dt.department_id
	)
SELECT 
	department_id, department_name, parent_department_id, depth, path
FROM department_tree
ORDER BY path;

d.sql案例详解：

这个查询使用了递归公共表达式来遍历公司部门关系。公共表达式使用了两个 SELECT 语句：

第一个 SELECT 语句选取了所有没有父部门的根部门，并将它们添加到临时表 department_tree 中。它们的深度被初始化为 1，并且它们的路径被设置为它们的部门 ID。这个 SELECT 语句是递归查询的起点。

第二个 SELECT 语句连接了 company_department 表和 department_tree 表。它选取了 company_department 表中所有具有父部门的部门，并连接到 department_tree 表中已经存在的部门。对于每个连接的行，它们的深度是父部门的深度加 1，并且它们的路径是父部门的路径加上逗号和它们自己的部门 ID。

查询返回了 department_tree 表中所有的部门，按照它们的路径排序。这个排序方法使得在结果集中，每个部门都在它们的父部门之后，并且它们的顺序是深度优先遍历的顺序。

e.查询结果截图：