【Sql递归查询】Mysql、Oracle、SQL Server、PostgreSQL 实现递归查询的区 ...

美食家大橙子  金牌会员 | 2025-1-16 05:37:54 | 显示全部楼层 | 阅读模式
打印 上一主题 下一主题
楼主

主题 903|帖子 903|积分 2709



  
更多相关内容可查看
Mysql 5.7 递归查询

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

  • 使用自连接实现递归查询
通过自连接的方式模仿递归查询，适合处理简单的递归结构。假设我们有一个表示部分层级关系的表 departments，结构如下：
  1. CREATE TABLE departments (
  2.     id INT PRIMARY KEY,
  3.     name VARCHAR(50),
  4.     parent_id INT
  5. );
复制代码
向表中插入一些示例数据：
  1. INSERT INTO departments (id, name, parent_id) VALUES
  2. (1, '总公司', NULL),
  3. (2, '研发部', 1),
  4. (3, '市场部', 1),
  5. (4, '研发一组', 2),
  6. (5, '研发二组', 2);
复制代码
使用自连接查询全部部分及其子部分：

  1. SELECT
  2.     t1.id AS root_id,
  3.     t1.name AS root_name,
  4.     t2.id AS child_id,
  5.     t2.name AS child_name
  6. FROM
  7.     departments t1
  8. JOIN
  9.     departments t2
  10. ON
  11.     t1.id = t2.parent_id
  12. UNION
  13. SELECT
  14.     id AS root_id,
  15.     name AS root_name,
  16.     id AS child_id,
  17.     name AS child_name
  18. FROM
  19.     departments
  20. WHERE
  21.     parent_id IS NULL;
复制代码
在这个查询中，通过 JOIN 语句将父部分和子部分关联起来，然后使用 UNION 操作符将顶级部分（parent_id 为 NULL）也包含在效果中。

  • 使用存储过程实现递归查询
  1. DELIMITER //
  3. -- 创建一个名为 recursive_departments_func 的函数，该函数接收两个整数参数 p_parent_id 和 p_level，并返回一个整数
  4. CREATE FUNCTION recursive_departments_func(p_parent_id INT, p_level INT) RETURNS INT
  5. DETERMINISTIC
  6. BEGIN
  7.     -- 声明一个整数变量 done，用于标记游标是否已经完成遍历，初始值为 FALSE
  8.     DECLARE done INT DEFAULT FALSE;
  9.     -- 声明一个整数变量 v_id，用于存储从游标中获取的部门 id
  10.     DECLARE v_id INT;
  11.     -- 声明一个字符串变量 v_name，用于存储从游标中获取的部门名称
  12.     DECLARE v_name VARCHAR(50);
  13.     -- 声明一个游标 cur，用于查询 departments 表中 parent_id 等于 p_parent_id 的记录
  14.     DECLARE cur CURSOR FOR
  15.         SELECT id, name FROM departments WHERE parent_id = p_parent_id;
  16.     -- 声明一个继续处理程序，当游标没有更多数据时，将 done 置为 TRUE
  17.     DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  19.     -- 创建一个临时表 temp_departments，用于存储递归调用的结果
  20.     -- 仅在该表不存在时创建，包含三个列：id、name 和 level
  21.     CREATE TEMPORARY TABLE IF NOT EXISTS temp_departments (
  22.         id INT,
  23.         name VARCHAR(50),
  24.         level INT
  25.     );
  28.     -- 打开游标 cur，以便开始读取数据
  29.     OPEN cur;
  31.     -- 定义一个名为 read_loop 的循环标签
  32.     read_loop: LOOP
  33.         -- 从游标 cur 中获取数据并存储到 v_id 和 v_name 中
  34.         FETCH cur INTO v_id, v_name;
  35.         -- 检查 done 变量是否为 TRUE，如果是则离开循环
  36.         IF done THEN
  37.             LEAVE read_loop;
  38.         END IF;
  40.         -- 将当前部门的信息插入到临时表 temp_departments 中
  41.         INSERT INTO temp_departments (id, name, level) VALUES (v_id, v_name, p_level);
  43.         -- 递归调用 recursive_departments_func 函数，将当前部门的 id 作为新的父部门 id，层级加 1
  44.         SET @result = recursive_departments_func(v_id, p_level + 1);
  45.     END LOOP;
  47.     -- 关闭游标 cur
  48.     CLOSE cur;
  50.     -- 函数最终返回 1
  51.     RETURN 1;
  52. END //
  54. DELIMITER ;
复制代码
Mysql 8 实现递归查询

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

  • 创建示例数据
假设我们有一个表示员工层级关系的表 employees，其中包含员工编号、姓名、上级员工编号：
  1. -- 创建表
  2. CREATE TABLE employees (
  3.     employee_id INT PRIMARY KEY,
  4.     name VARCHAR(50),
  5.     manager_id INT,
  6.     FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
  7. );
  9. -- 插入数据
  10. INSERT INTO employees (employee_id, name, manager_id) VALUES
  11. (1, 'CEO', NULL),
  12. (2, 'CTO', 1),
  13. (3, 'CFO', 1),
  14. (4, 'Lead Developer', 2),
  15. (5, 'Developer 1', 4),
  16. (6, 'Developer 2', 4),
  17. (7, 'Accountant', 3);
复制代码

  • 递归查询全部员工及其部属
使用 WITH RECURSIVE 子句进行递归查询，查找某个员工及其全部部属。以下是查询 CEO 及其全部部属的示例：
  1. WITH RECURSIVE employee_hierarchy AS (
  2.     -- 初始查询，找到CEO
  3.     SELECT employee_id, name, manager_id
  4.     FROM employees
  5.     WHERE name = 'CEO'
  6.     UNION ALL
  7.     -- 递归部分，找到下属员工
  8.     SELECT e.employee_id, e.name, e.manager_id
  9.     FROM employees e
  10.     INNER JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
  11. )
  12. SELECT * FROM employee_hierarchy;
复制代码


  • 2.1. CTE（公共表表达式）定义：

    • WITH RECURSIVE employee_hierarchy AS (...) 定义了一个名为 employee_hierarchy 的递归 CTE。
    • 初始查询部分：
      1. SELECT employee_id, name, manager_id
      2. FROM employees
      3. WHERE name = 'CEO'
      复制代码
      这部分找到 CEO 的纪录，作为递归的出发点。
    • UNION ALL 用于将初始查询效果和递归查询效果合并。
    • 递归部分：
      1. SELECT e.employee_id, e.name, e.manager_id
      2. FROM employees e
      3. INNER JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
      复制代码
      这部分通过连接 employees 表和递归生成的 employee_hierarchy 表，找到每个员工的部属。


  • 反向递归查询（查找某个员工的全部上级）
查找某个员工（比方 Developer 1）的全部上级：
  1. WITH RECURSIVE manager_hierarchy AS (
  2.     -- 初始查询，找到Developer 1
  3.     SELECT employee_id, name, manager_id
  4.     FROM employees
  5.     WHERE name = 'Developer 1'
  6.     UNION ALL
  7.     -- 递归部分，找到上级员工
  8.     SELECT e.employee_id, e.name, e.manager_id
  9.     FROM employees e
  10.     INNER JOIN manager_hierarchy mh ON e.employee_id = mh.manager_id
  11. )
  12. SELECT * FROM manager_hierarchy;
复制代码
这个查询同样使用 WITH RECURSIVE，但递归方向是从指定员工向上查找其全部上级。

  • 组织递归查询示例
  1. -- 假设我们有一个 organizations 表存储组织信息
  2. CREATE TABLE organizations (
  3.     id INT PRIMARY KEY AUTO_INCREMENT,
  4.     parent_id INT,
  5.     name VARCHAR(255),
  6.     level INT
  7. );
  9. -- 假设我们有一个 employees 表存储员工信息
  10. CREATE TABLE employees (
  11.     id INT PRIMARY KEY AUTO_INCREMENT,
  12.     name VARCHAR(255),
  13.     organization_id INT
  14. );
  16. -- 插入一些示例数据到 organizations 表
  17. INSERT INTO organizations (parent_id, name, level) VALUES
  18.     (NULL, '总公司', 1),
  19.     (1, '分公司 A', 2),
  20.     (1, '分公司 B', 2),
  21.     (2, '部门 A1', 3),
  22.     (2, '部门 A2', 3),
  23.     (3, '部门 B1', 3),
  24.     (3, '部门 B2', 3),
  25.     (4, '小组 A1-1', 4),
  26.     (4, '小组 A1-2', 4);
  28. -- 插入一些示例数据到 employees 表
  29. INSERT INTO employees (name, organization_id) VALUES
  30.     ('员工 1', 1),
  31.     ('员工 2', 2),
  32.     ('员工 3', 2),
  33.     ('员工 4', 3),
  34.     ('员工 5', 4),
  35.     ('员工 6', 4),
  36.     ('员工 7', 4),
  37.     ('员工 8', 5),
  38.     ('员工 9', 6),
  39.     ('员工 10', 7),
  40.     ('员工 11', 8);
  42. -- 使用 WITH RECURSIVE 进行递归查询
  43. WITH RECURSIVE organization_hierarchy AS (
  44.     -- 非递归部分：选择根组织作为起始点
  45.     SELECT id, parent_id, name, level, 0 AS depth
  46.     FROM organizations
  47.     WHERE id = 1
  48.     UNION ALL
  49.     -- 递归部分：选择子组织，深度加 1
  50.     SELECT o.id, o.parent_id, o.name, o.level, oh.depth + 1
  51.     FROM organizations o
  52.     JOIN organization_hierarchy oh ON o.parent_id = oh.id
  53. )
  54. -- 从递归结果中选择信息并统计员工数量
  55. SELECT oh.id, oh.parent_id, oh.name, oh.level, oh.depth, COUNT(e.id) AS employee_count
  56. FROM organization_hierarchy oh
  57. LEFT JOIN employees e ON oh.id = e.organization_id
  58. GROUP BY oh.id, oh.parent_id, oh.name, oh.level, oh.depth
  59. 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)。
  1. -- 使用 CONNECT BY 子句
  2. SELECT department_id, department_name, parent_department_id
  3. FROM departments
  4. START WITH parent_department_id IS NULL
  5. CONNECT BY PRIOR department_id = parent_department_id;
  7. -- 使用 WITH RECURSIVE 语法
  8. WITH RECURSIVE department_hierarchy AS (
  9.     SELECT department_id, department_name, parent_department_id
  10.     FROM departments
  11.     WHERE parent_department_id IS NULL
  12.     UNION ALL
  13.     SELECT d.department_id, d.department_name, d.parent_department_id
  14.     FROM departments d
  15.     INNER JOIN department_hierarchy dh ON d.parent_department_id = dh.department_id
  16. )
  17. SELECT * FROM department_hierarchy;
复制代码


  • 示例2：使用 CONNECT BY 和 START WITH 子句进行递归查询，以查询 id 为 1 的组织（总公司）及其全部子组织。
  1. CREATE TABLE organizations (
  2.     id        NUMBER PRIMARY KEY,
  3.     parent_id NUMBER,
  4.     name      VARCHAR2(100)
  5. );
  7. INSERT INTO organizations (id, parent_id, name) VALUES (1, NULL, '总公司');
  8. INSERT INTO organizations (id, parent_id, name) VALUES (2, 1, '分公司 A');
  9. INSERT INTO organizations (id, parent_id, name) VALUES (3, 1, '分公司 B');
  10. INSERT INTO organizations (id, parent_id, name) VALUES (4, 2, '部门 A1');
  11. INSERT INTO organizations (id, parent_id, name) VALUES (5, 2, '部门 A2');
  12. INSERT INTO organizations (id, parent_id, name) VALUES (6, 3, '部门 B1');
  13. INSERT INTO organizations (id, parent_id, name) VALUES (7, 3, '部门 B2');
  14. INSERT INTO organizations (id, parent_id, name) VALUES (8, 4, '小组 A1-1');
  15. INSERT INTO organizations (id, parent_id, name) VALUES (9, 4, '小组 A1-2');
  17. SELECT o.id, o.parent_id, o.name, LEVEL
  18. FROM organizations o
  19. START WITH o.id = 1
  20. CONNECT BY PRIOR o.id = o.parent_id;
复制代码


  • 示例3：使用递归查询和 JOIN 操作计算每个组织及其子组织的员工总数。
  1. CREATE TABLE employees (
  2.     id           NUMBER PRIMARY KEY,
  3.     name         VARCHAR2(100),
  4.     organization_id NUMBER
  5. );
  7. INSERT INTO employees (id, name, organization_id) VALUES (1, '员工 1', 2);
  8. INSERT INTO employees (id, name, organization_id) VALUES (2, '员工 2', 2);
  9. INSERT INTO employees (id, name, organization_id) VALUES (3, '员工 3', 3);
  10. INSERT INTO employees (id, name, organization_id) VALUES (4, '员工 4', 4);
  11. INSERT INTO employees (id, name, organization_id) VALUES (5, '员工 5', 4);
  12. INSERT INTO employees (id, name, organization_id) VALUES (6, '员工 6', 5);
  13. INSERT INTO employees (id, name, organization_id) VALUES (7, '员工 7', 6);
  14. INSERT INTO employees (id, name, organization_id) VALUES (8, '员工 8', 7);
  15. INSERT INTO employees (id, name, organization_id) VALUES (9, '员工 9', 8);
  18. WITH org_hierarchy AS (
  19.     SELECT o.id, o.parent_id, o.name, LEVEL AS org_level
  20.     FROM organizations o
  21.     START WITH o.id = 1
  22.     CONNECT BY PRIOR o.id = o.parent_id
  23. )
  24. SELECT oh.id, oh.parent_id, oh.name, oh.org_level, COUNT(e.id) AS employee_count
  25. FROM org_hierarchy oh
  26. LEFT JOIN employees e ON oh.id = e.organization_id
  27. GROUP BY oh.id, oh.parent_id, oh.name, oh.org_level
  28. ORDER BY oh.org_level, oh.id;
复制代码


  • 示例4：假设 organizations 表有一个 budget 列表示组织的预算，而且预算可以从父组织分配给子组织。我们可以使用递归查询计算每个组织及其子组织的最终预算
  1. ALTER TABLE organizations ADD (budget NUMBER);
  3. UPDATE organizations SET budget = 100000 WHERE id = 1;
  4. UPDATE organizations SET budget = 0 WHERE id IN (2, 3);
  5. UPDATE organizations SET budget = 0 WHERE id IN (4, 5, 6, 7);
  6. UPDATE organizations SET budget = 0 WHERE id IN (8, 9);
  8. WITH budget_allocation AS (
  9.     SELECT o.id, o.parent_id, o.name, o.budget AS original_budget,
  10.            o.budget AS allocated_budget, LEVEL AS org_level
  11.     FROM organizations o
  12.     START WITH o.id = 1
  13.     CONNECT BY PRIOR o.id = o.parent_id
  14. )
  15. SELECT ba.id, ba.parent_id, ba.name, ba.original_budget,
  16.        CASE
  17.            WHEN ba.original_budget = 0 THEN
  18.                NVL((LAG(ba.allocated_budget) OVER (ORDER BY ba.org_level DESC) / COUNT(*) OVER (PARTITION BY ba.parent_id)), 0)
  19.            ELSE ba.allocated_budget
  20.        END AS final_budget,
  21.        ba.org_level
  22. FROM budget_allocation ba;
复制代码
SQL Server 递归查询示例



  • 支持版本：SQL Server 2005 开始支持 WITH 子句，包罗递归 CTE（Common Table Expressions）。
  • 示例：假设有一个员工表 Employees，结构为 (EmployeeID, Name, ManagerID)。
  1. WITH RECURSIVE EmployeeHierarchy AS (
  2.     SELECT EmployeeID, Name, ManagerID
  3.     FROM Employees
  4.     WHERE ManagerID IS NULL
  5.     UNION ALL
  6.     SELECT e.EmployeeID, e.Name, e.ManagerID
  7.     FROM Employees e
  8.     INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
  9. )
  10. SELECT * FROM EmployeeHierarchy;
复制代码
PostgreSQL 递归查询示例



  • 支持版本：PostgreSQL 8.4 开始支持递归 CTE（WITH RECURSIVE）。
  • 示例：假设有一个表示菜单层级关系的表 menus，结构为 (menu_id, menu_name, parent_menu_id)。
  1. WITH RECURSIVE menu_hierarchy AS (
  2.     SELECT menu_id, menu_name, parent_menu_id
  3.     FROM menus
  4.     WHERE parent_menu_id IS NULL
  5.     UNION ALL
  6.     SELECT m.menu_id, m.menu_name, m.parent_menu_id
  7.     FROM menus m
  8.     INNER JOIN menu_hierarchy mh ON m.parent_menu_id = mh.menu_id
  9. )
  10. SELECT * FROM menu_hierarchy;
复制代码
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

本帖子中包含更多资源

您需要 登录 才可以下载或查看，没有账号？立即注册

x
回复

使用道具 举报

0 个回复

倒序浏览
返回列表

快速回复

高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 or 立即注册

本版积分规则

美食家大橙子

金牌会员
这个人很懒什么都没写!

楼主热帖

标签云

存储 挺好的 服务器
微信订阅号
微信服务号
微信客服
小程序
H5
关于我们 商务合作 网站地图
©Copyright 2022-2024 杭州祥升科技有限公司 版权所有 浙ICP备20004199号
快速回复 返回顶部 返回列表