当前位置：首页 > news >正文

MySQL递归查询：洞悉数据的层层关联

news 来源：原创 2024/5/16 6:19:13

在处理关系型数据库时，我们经常会遇到这样的情况：某些数据之间存在层级关系，例如目录、组织结构、评论等。在这些场景下，我们需要一种灵活的查询技术来处理这种层级关系。今天我们就来探讨MySQL中的递归查询，体验其独特的魅力，并展示两个实用的示例。

一、递归查询简介

二、递归查询的基本语法

三、MySQL递归查询示例

四、递归查询的另一个应用

一、递归查询简介

递归查询是一种在数据库中处理具有层次结构的数据的方法，它使用带有自连接的表和公共表表达式（Common Table Expression，简称CTE），让我们可以在一个表中查询出具有父子关系的数据。在MySQL中，我们可以使用WITH RECURSIVE语句来实现递归查询。

二、递归查询的基本语法

在MySQL中，递归查询的基本语法如下：

WITH RECURSIVE cte_name (列1, 列2, ...) AS  (  -- 非递归部分，用于初始化cte（公共表表达式）SELECT 列1, 列2, ... FROM 表名 WHERE 初始查询条件UNION ALL  -- 递归部分，用于扩展cteSELECT 列1, 列2, ... FROM 表名 WHERE ...
)  
SELECT 列1, 列2, ... FROM cte_name;

WITH RECURSIVE：这是递归查询的关键字，用于定义递归查询。

cte_name：这是为递归查询定义的名称，方便后续引用。

三、MySQL递归查询示例

下面是一个简单的示例，演示如何使用MySQL进行递归查询。假设我们有一个包含员工和他们的上级的表，如下所示：

CREATE TABLE employees (id INT PRIMARY KEY,name VARCHAR(100),manager_id INT,FOREIGN KEY (manager_id) REFERENCES employees (id)
);

插入测试数据：

INSERT INTO employees (id, name, manager_id)
VALUES (1, '张三', NULL),(2, '李四', 1),(3, '王五', 2),(4, '赵六', 2),(5, '孙七', 3),(6, '周八', 3),(7, '吴九', 5),(8, '郑十', 6);

我们的需求是查找某个特定员工的所有下级。通过递归查询实现：

WITH RECURSIVE emp AS (SELECT id, name, manager_idFROM employeesWHERE name = '王五'UNION ALLSELECT e.id, e.name, e.manager_idFROM employees e, empWHERE e.manager_id = emp.id
)
SELECT id, name FROM emp;

得到的结果如图：

这个示例中的查询它包含两个部分：非递归部分和递归部分。非递归部分是从员工表中选择某个员工，递归部分是通过与公共表表达式进行连接从员工表中选择下属员工。最后，从公共表表达式中查询整个员工层级结构。

四、递归查询的另一个应用

使用递归查询可以生成指定数量的序列，如下SQL生成1~10的序列：

WITH RECURSIVE seq(seq_no) AS (SELECT 1UNION ALLSELECT 1 + seq_no FROM seq WHERE seq_no < 10)
SELECT * FROM seq;

那么，生成这个序列有什么用呢？有很多场景需要用到这种序列，如：统计每年在校学生人数。

假设有一个招生人数表，记录了每年招生人数和学生学制等信息，现需要统计每年在校学生人数。

我们仍然使用Excel表格辅助分析，为该问题编写SQL，先在Excel里面输入样例数据：

先统一约定，假设本例中的统计时间为下半年，即：某一入学年度的招生人数，会统计到在校人数中，当年毕业的学生，不会统计到在校人数中。

为了统计某一年在校学生人数，我们在该数据后面添加辅助数据，比如统计2023年在校学生人数，填入如下数据：

学生在校状态，是根据入学年度和学制计算出毕业时间，然后与统计年度进行比较得出。筛选出状态为在校的数据然后求和即可。

但本次的需求是统计每年在校学生人数，也就是需要为每一个统计年度生成这样的数据，如下图所示：

分析这些数据的规律，某一入学年度的数据，在入学年度及之后的每一个统计年度中，如果该入学年度的学生在校，则该数据需要出现在该统计年度中，学生在校多少年，该入学年度的数据就会出现多少次。而连续的统计年度，就是一个序列！

用以下SQL模拟招生人数表数据：

SELECT 2020 year, 300 enrollment, 3 length_of_schooling
UNION ALL SELECT 2021, 400, 4
UNION ALL SELECT 2022, 400, 4
UNION ALL SELECT 2023, 400, 4

将该数据与递归产生的序列连接，就可以得到前面需要的每一个统计年度的招生数据。为便于计算统计年度，序列从0开始，序列最大值为学制最大值：

WITH RECURSIVE seq(seq_no) AS (SELECT 0UNION ALLSELECT 1 + seq_no FROM seq WHERE seq_no < 4
), cnt AS (SELECT 2020 enro_year, 300 enrollment, 3 length_of_schoolingUNION ALL SELECT 2021, 400, 4UNION ALL SELECT 2022, 400, 4UNION ALL SELECT 2023, 400, 4)
SELECT cnt.*, enro_year + seq_no stat_year,IF(seq_no < length_of_schooling, '在校', '毕业') status
FROM cnt, seq
-- WHERE seq_no < length_of_schooling
ORDER BY enro_year + seq_no, enro_year;

只需要将上述SQL稍做修改，按统计年度分组统计，就可以得到每年的在校学生人数：

WITH RECURSIVE seq(seq_no) AS (SELECT 0UNION ALLSELECT 1 + seq_no FROM seq WHERE seq_no < 4
), cnt AS (SELECT 2020 enro_year, 300 enrollment, 3 length_of_schoolingUNION ALL SELECT 2021, 400, 4UNION ALL SELECT 2022, 400, 4UNION ALL SELECT 2023, 400, 4)
SELECT enro_year + seq_no stat_year, sum(enrollment) stu_enrollment 
FROM cnt, seq
WHERE seq_no < length_of_schooling
GROUP BY enro_year + seq_no
ORDER BY enro_year + seq_no;

得到的结果如图：