怎样建立销售网站,垂直电商网站如何做内容运营,门户网站简单模板,福州网站备案在处理关系型数据库时#xff0c;我们经常会遇到这样的情况#xff1a;某些数据之间存在层级关系#xff0c;例如目录、组织结构、评论等。在这些场景下#xff0c;我们需要一种灵活的查询技术来处理这种层级关系。今天我们就来探讨MySQL中的递归查询#xff0c;体验其独特…在处理关系型数据库时我们经常会遇到这样的情况某些数据之间存在层级关系例如目录、组织结构、评论等。在这些场景下我们需要一种灵活的查询技术来处理这种层级关系。今天我们就来探讨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;
得到的结果如图 通过使用递归查询我们可以轻松地解决一些传统查询方法难以处理的问题。通过本文的介绍和示例希望能够帮助大家更好地理解和应用MySQL中的递归查询。
