【Sql递归查询】Mysql、Oracle、SQL Server、PostgreSQL 实现递归查询的区别与案例（详解）

devtools/2025/1/17 14:27:30/

在这里插入图片描述

文章目录


更多相关内容可查看

sql_57__8">Mysql 5.7 递归查询

MySQL 5.7 本身不直接支持标准 SQL 中的递归查询语法（如 WITH RECURSIVE 这种常见的递归查询方式），但可以通过使用存储过程、临时表或自连接等方式来实现递归查询的效果。

  1. 使用自连接实现递归查询

通过自连接的方式模拟递归查询，适合处理简单的递归结构。假设我们有一个表示部门层级关系的表 departments，结构如下：

sql">CREATE TABLE departments (id INT PRIMARY KEY,name VARCHAR(50),parent_id INT
);

向表中插入一些示例数据：

sql">INSERT INTO departments (id, name, parent_id) VALUES
(1, '总公司', NULL),
(2, '研发部', 1),
(3, '市场部', 1),
(4, '研发一组', 2),
(5, '研发二组', 2);

使用自连接查询所有部门及其子部门：

在这里插入图片描述

sql">SELECTt1.id AS root_id,t1.name AS root_name,t2.id AS child_id,t2.name AS child_name
FROMdepartments t1
JOINdepartments t2
ONt1.id = t2.parent_id
UNION
SELECTid AS root_id,name AS root_name,id AS child_id,name AS child_name
FROMdepartments
WHEREparent_id IS NULL;

在这个查询中，通过 JOIN 语句将父部门和子部门关联起来，然后使用 UNION 操作符将顶级部门（parent_id 为 NULL）也包含在结果中。

  1. 使用存储过程实现递归查询
sql">DELIMITER //-- 创建一个名为 recursive_departments_func 的函数，该函数接收两个整数参数 p_parent_id 和 p_level，并返回一个整数
CREATE FUNCTION recursive_departments_func(p_parent_id INT, p_level INT) RETURNS INT
DETERMINISTIC
BEGIN-- 声明一个整数变量 done，用于标记游标是否已经完成遍历，初始值为 FALSEDECLARE done INT DEFAULT FALSE;-- 声明一个整数变量 v_id，用于存储从游标中获取的部门 idDECLARE v_id INT;-- 声明一个字符串变量 v_name，用于存储从游标中获取的部门名称DECLARE v_name VARCHAR(50);-- 声明一个游标 cur，用于查询 departments 表中 parent_id 等于 p_parent_id 的记录DECLARE cur CURSOR FORSELECT id, name FROM departments WHERE parent_id = p_parent_id;-- 声明一个继续处理程序，当游标没有更多数据时，将 done 置为 TRUEDECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;-- 创建一个临时表 temp_departments，用于存储递归调用的结果-- 仅在该表不存在时创建，包含三个列：id、name 和 levelCREATE TEMPORARY TABLE IF NOT EXISTS temp_departments (id INT,name VARCHAR(50),level INT);-- 打开游标 cur，以便开始读取数据OPEN cur;-- 定义一个名为 read_loop 的循环标签read_loop: LOOP-- 从游标 cur 中获取数据并存储到 v_id 和 v_name 中FETCH cur INTO v_id, v_name;-- 检查 done 变量是否为 TRUE，如果是则离开循环IF done THENLEAVE read_loop;END IF;-- 将当前部门的信息插入到临时表 temp_departments 中INSERT INTO temp_departments (id, name, level) VALUES (v_id, v_name, p_level);-- 递归调用 recursive_departments_func 函数，将当前部门的 id 作为新的父部门 id，层级加 1SET @result = recursive_departments_func(v_id, p_level + 1);END LOOP;-- 关闭游标 curCLOSE cur;-- 函数最终返回 1RETURN 1;
END //DELIMITER ;

sql_8__122">Mysql 8 实现递归查询

在 MySQL 8 中，可以使用 WITH RECURSIVE 子句来实现递归查询。

  1. 创建示例数据

假设我们有一个表示员工层级关系的表 employees，其中包含员工编号、姓名、上级员工编号：

sql">-- 创建表
CREATE TABLE employees (employee_id INT PRIMARY KEY,name VARCHAR(50),manager_id INT,FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
);-- 插入数据
INSERT INTO employees (employee_id, name, manager_id) VALUES
(1, 'CEO', NULL),
(2, 'CTO', 1),
(3, 'CFO', 1),
(4, 'Lead Developer', 2),
(5, 'Developer 1', 4),
(6, 'Developer 2', 4),
(7, 'Accountant', 3);
  1. 递归查询所有员工及其下属

使用 WITH RECURSIVE 子句进行递归查询，查找某个员工及其所有下属。以下是查询 CEO 及其所有下属的示例：

sql">WITH RECURSIVE employee_hierarchy AS (-- 初始查询，找到CEOSELECT employee_id, name, manager_idFROM employeesWHERE name = 'CEO'UNION ALL-- 递归部分，找到下属员工SELECT e.employee_id, e.name, e.manager_idFROM employees eINNER JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy;
  • 2.1. CTE（公共表表达式）定义：
    • WITH RECURSIVE employee_hierarchy AS (...) 定义了一个名为 employee_hierarchy 的递归 CTE。
    • 初始查询部分：
      sql">SELECT employee_id, name, manager_id
FROM employees
WHERE name = 'CEO'
      这部分找到 CEO 的记录，作为递归的起点。
    • UNION ALL 用于将初始查询结果和递归查询结果合并。
    • 递归部分：
      sql">SELECT e.employee_id, e.name, e.manager_id
FROM employees e
INNER JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
      这部分通过连接 employees 表和递归生成的 employee_hierarchy 表，找到每个员工的下属。
  1. 反向递归查询（查找某个员工的所有上级）

查找某个员工（例如 Developer 1）的所有上级：

sql">WITH RECURSIVE manager_hierarchy AS (-- 初始查询，找到Developer 1SELECT employee_id, name, manager_idFROM employeesWHERE name = 'Developer 1'UNION ALL-- 递归部分，找到上级员工SELECT e.employee_id, e.name, e.manager_idFROM employees eINNER JOIN manager_hierarchy mh ON e.employee_id = mh.manager_id
)
SELECT * FROM manager_hierarchy;

这个查询同样使用 WITH RECURSIVE，但递归方向是从指定员工向上查找其所有上级。

  1. 组织递归查询示例
sql">-- 假设我们有一个 organizations 表存储组织信息
CREATE TABLE organizations (id INT PRIMARY KEY AUTO_INCREMENT,parent_id INT,name VARCHAR(255),level INT
);-- 假设我们有一个 employees 表存储员工信息
CREATE TABLE employees (id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(255),organization_id INT
);-- 插入一些示例数据到 organizations 表
INSERT INTO organizations (parent_id, name, level) VALUES(NULL, '总公司', 1),(1, '分公司 A', 2),(1, '分公司 B', 2),(2, '部门 A1', 3),(2, '部门 A2', 3),(3, '部门 B1', 3),(3, '部门 B2', 3),(4, '小组 A1-1', 4),(4, '小组 A1-2', 4);-- 插入一些示例数据到 employees 表
INSERT INTO employees (name, organization_id) VALUES('员工 1', 1),('员工 2', 2),('员工 3', 2),('员工 4', 3),('员工 5', 4),('员工 6', 4),('员工 7', 4),('员工 8', 5),('员工 9', 6),('员工 10', 7),('员工 11', 8);-- 使用 WITH RECURSIVE 进行递归查询
WITH RECURSIVE organization_hierarchy AS (-- 非递归部分：选择根组织作为起始点SELECT id, parent_id, name, level, 0 AS depthFROM organizationsWHERE id = 1UNION ALL-- 递归部分：选择子组织，深度加 1SELECT o.id, o.parent_id, o.name, o.level, oh.depth + 1FROM organizations oJOIN organization_hierarchy oh ON o.parent_id = oh.id
)
-- 从递归结果中选择信息并统计员工数量
SELECT oh.id, oh.parent_id, oh.name, oh.level, oh.depth, COUNT(e.id) AS employee_count
FROM organization_hierarchy oh
LEFT JOIN employees e ON oh.id = e.organization_id
GROUP BY oh.id, oh.parent_id, oh.name, oh.level, oh.depth
ORDER BY oh.depth, oh.id;

Oracle递归示例

  • 支持版本：Oracle 9i 开始引入递归查询的功能，通过 CONNECT BY 子句实现。从 Oracle 11g 开始支持使用 WITH RECURSIVE 语法（CTE 递归查询）。
  • 示例1：假设有一个表示部门层级关系的表 departments，结构为 (department_id, department_name, parent_department_id)。
sql">-- 使用 CONNECT BY 子句
SELECT department_id, department_name, parent_department_id
FROM departments
START WITH parent_department_id IS NULL
CONNECT BY PRIOR department_id = parent_department_id;-- 使用 WITH RECURSIVE 语法
WITH RECURSIVE department_hierarchy AS (SELECT department_id, department_name, parent_department_idFROM departmentsWHERE parent_department_id IS NULLUNION ALLSELECT d.department_id, d.department_name, d.parent_department_idFROM departments dINNER JOIN department_hierarchy dh ON d.parent_department_id = dh.department_id
)
SELECT * FROM department_hierarchy;
  • 示例2：使用 CONNECT BY 和 START WITH 子句进行递归查询，以查询 id 为 1 的组织（总公司）及其所有子组织。
sql">CREATE TABLE organizations (id        NUMBER PRIMARY KEY,parent_id NUMBER,name      VARCHAR2(100)
);INSERT INTO organizations (id, parent_id, name) VALUES (1, NULL, '总公司');
INSERT INTO organizations (id, parent_id, name) VALUES (2, 1, '分公司 A');
INSERT INTO organizations (id, parent_id, name) VALUES (3, 1, '分公司 B');
INSERT INTO organizations (id, parent_id, name) VALUES (4, 2, '部门 A1');
INSERT INTO organizations (id, parent_id, name) VALUES (5, 2, '部门 A2');
INSERT INTO organizations (id, parent_id, name) VALUES (6, 3, '部门 B1');
INSERT INTO organizations (id, parent_id, name) VALUES (7, 3, '部门 B2');
INSERT INTO organizations (id, parent_id, name) VALUES (8, 4, '小组 A1-1');
INSERT INTO organizations (id, parent_id, name) VALUES (9, 4, '小组 A1-2');SELECT o.id, o.parent_id, o.name, LEVEL
FROM organizations o
START WITH o.id = 1
CONNECT BY PRIOR o.id = o.parent_id;
  • 示例3：使用递归查询和 JOIN 操作计算每个组织及其子组织的员工总数。
sql">CREATE TABLE employees (id           NUMBER PRIMARY KEY,name         VARCHAR2(100),organization_id NUMBER
);INSERT INTO employees (id, name, organization_id) VALUES (1, '员工 1', 2);
INSERT INTO employees (id, name, organization_id) VALUES (2, '员工 2', 2);
INSERT INTO employees (id, name, organization_id) VALUES (3, '员工 3', 3);
INSERT INTO employees (id, name, organization_id) VALUES (4, '员工 4', 4);
INSERT INTO employees (id, name, organization_id) VALUES (5, '员工 5', 4);
INSERT INTO employees (id, name, organization_id) VALUES (6, '员工 6', 5);
INSERT INTO employees (id, name, organization_id) VALUES (7, '员工 7', 6);
INSERT INTO employees (id, name, organization_id) VALUES (8, '员工 8', 7);
INSERT INTO employees (id, name, organization_id) VALUES (9, '员工 9', 8);WITH org_hierarchy AS (SELECT o.id, o.parent_id, o.name, LEVEL AS org_levelFROM organizations oSTART WITH o.id = 1CONNECT BY PRIOR o.id = o.parent_id
)
SELECT oh.id, oh.parent_id, oh.name, oh.org_level, COUNT(e.id) AS employee_count
FROM org_hierarchy oh
LEFT JOIN employees e ON oh.id = e.organization_id
GROUP BY oh.id, oh.parent_id, oh.name, oh.org_level
ORDER BY oh.org_level, oh.id;
  • 示例4：假设 organizations 表有一个 budget 列表示组织的预算，并且预算可以从父组织分配给子组织。我们可以使用递归查询计算每个组织及其子组织的最终预算
sql">ALTER TABLE organizations ADD (budget NUMBER);UPDATE organizations SET budget = 100000 WHERE id = 1;
UPDATE organizations SET budget = 0 WHERE id IN (2, 3);
UPDATE organizations SET budget = 0 WHERE id IN (4, 5, 6, 7);
UPDATE organizations SET budget = 0 WHERE id IN (8, 9);WITH budget_allocation AS (SELECT o.id, o.parent_id, o.name, o.budget AS original_budget,o.budget AS allocated_budget, LEVEL AS org_levelFROM organizations oSTART WITH o.id = 1CONNECT BY PRIOR o.id = o.parent_id
)
SELECT ba.id, ba.parent_id, ba.name, ba.original_budget,CASEWHEN ba.original_budget = 0 THENNVL((LAG(ba.allocated_budget) OVER (ORDER BY ba.org_level DESC) / COUNT(*) OVER (PARTITION BY ba.parent_id)), 0)ELSE ba.allocated_budgetEND AS final_budget,ba.org_level
FROM budget_allocation ba;

SQL Server 递归查询示例

  • 支持版本：SQL Server 2005 开始支持 WITH 子句，包括递归 CTE（Common Table Expressions）。
  • 示例：假设有一个员工表 Employees，结构为 (EmployeeID, Name, ManagerID)。
sql">WITH RECURSIVE EmployeeHierarchy AS (SELECT EmployeeID, Name, ManagerIDFROM EmployeesWHERE ManagerID IS NULLUNION ALLSELECT e.EmployeeID, e.Name, e.ManagerIDFROM Employees eINNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT * FROM EmployeeHierarchy;

PostgreSQL 递归查询示例

  • 支持版本：PostgreSQL 8.4 开始支持递归 CTE（WITH RECURSIVE）。
  • 示例：假设有一个表示菜单层级关系的表 menus，结构为 (menu_id, menu_name, parent_menu_id)。
sql">WITH RECURSIVE menu_hierarchy AS (SELECT menu_id, menu_name, parent_menu_idFROM menusWHERE parent_menu_id IS NULLUNION ALLSELECT m.menu_id, m.menu_name, m.parent_menu_idFROM menus mINNER JOIN menu_hierarchy mh ON m.parent_menu_id = mh.menu_id
)
SELECT * FROM menu_hierarchy;

http://www.ppmy.cn/devtools/151294.html

相关文章

持续集成 01|Gitee介绍、Pycharm使用Gitee

持续集成 01|Gitee介绍、Pycharm使用Gitee

目录 一、理论 二、 git的简介与安装 三、Gitee 1、注册网易163邮箱 2、注册Gitee账号 3、git和gitee管理代码工作原理 三、PyCharm安装配置Gitee 四、Pycharm使用Gitee插件的五种场景 1、将 Gitee的新仓库 Checkout（检出）到 Pycharm中 2、推送…
阅读更多...
关于二叉树深搜的算法v2.1

关于二叉树深搜的算法v2.1

2331. 计算布尔二叉树的值 1、宏观 给定一个根节点，在进行运算结果前首先要知道该根节点左子树的整体结果和右子树的整体结果才能进行运算。 2、细节dfs 从上往下遍历，从下往上计算，遍历停止开始算的结果是遍历到叶子结点。 class Solution…
阅读更多...
【微服务】SpringBoot 通用异常处理方案使用详解

【微服务】SpringBoot 通用异常处理方案使用详解

目录 一、前言 二、SpringBoot 异常介绍 2.1 SpringBoot 中异常定义 2.1.1 SpringBoot 异常处理机制的重要性 2.2 常用的异常分类 2.3 常用的异常处理解决方案 三、springboot 异常处理操作实践 3.1 springboot自适应错误处理机制 3.1.1 使用默认错误页面 3.1.2 自定义…
阅读更多...
Windows部署NVM并下载多版本Node.js的方法（含删除原有Node的方法）

Windows部署NVM并下载多版本Node.js的方法（含删除原有Node的方法）

本文介绍在Windows电脑中，下载、部署NVM（node.js version management）环境，并基于其安装不同版本的Node.js的方法。 在之前的文章Windows系统下载、部署Node.js与npm环境的方法（https://blog.csdn.net/zhebushibiaoshi…
阅读更多...
4.Proto 3 语法详解

4.Proto 3 语法详解

目录 proto 3 语法详解字段规则消息类型的定义与使用创建通讯录2.0版本enum类型升级通讯录至2.1版本Any类型升级通讯录至2.2版本oneof类型升级通讯录至2.3版本map类型升级通讯录至2.4版本默认值更新消息保留字段reserved创建通讯录3.0版本未知字段升级通讯录3.1版本前后兼容性选…
阅读更多...
Java中JavaSE与JavaEE的区别

Java中JavaSE与JavaEE的区别

文章目录 Java中JavaSE与JavaEE的区别一、引言二、功能定位与应用场景1、JavaSE2、JavaEE 三、开发与部署1、JavaSE2、JavaEE 四、使用示例五、JavaEE常用框架1. Spring框架示例代码： 2. Hibernate框架示例代码： 3. Struts框架示例代码： 4. M…
阅读更多...
Mac的`~键打出来±§`？解析ANSI、ISO、JIS键盘标准的区别与布局

Mac的`~键打出来±§`？解析ANSI、ISO、JIS键盘标准的区别与布局

注：本文由deepseek生成，只进行了基本的校对。 引言 在使用Mac时，你是否遇到过这样的问题：按下~键，却输出了或？这其实是因为你的键盘类型设置与物理键盘布局不匹配。Mac支持多种键盘标准，包括A…
阅读更多...
Spring AI 对话记忆

Spring AI 对话记忆

对话记忆功能是一种技术，使得应用程序或智能助手能够在一段时间内“记住”用户提供的信息，以便在后续对话中参考和使用。这种记忆可以让对话更加连贯，并使助手能够理解用户的背景和偏好，从而提供更加个性化和精准的回复。 对话记…
阅读更多...
最新文章

Copyright @ 2022~2023