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

在这里插入图片描述

文章目录

    • Mysql 5.7 递归查询
    • Mysql 8 实现递归查询
    • Oracle递归示例
    • SQL Server 递归查询示例
    • PostgreSQL 递归查询示例

更多相关内容可查看

Mysql 5.7 递归查询

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

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

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

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

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

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

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

在这里插入图片描述

SELECT
    t1.id AS root_id,
    t1.name AS root_name,
    t2.id AS child_id,
    t2.name AS child_name
FROM
    departments t1
JOIN
    departments t2
ON
    t1.id = t2.parent_id
UNION
SELECT
    id AS root_id,
    name AS root_name,
    id AS child_id,
    name AS child_name
FROM
    departments
WHERE
    parent_id IS NULL;

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

  1. 使用存储过程实现递归查询
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，用于标记游标是否已经完成遍历，初始值为 FALSE
    DECLARE done INT DEFAULT FALSE;
    -- 声明一个整数变量 v_id，用于存储从游标中获取的部门 id
    DECLARE v_id INT;
    -- 声明一个字符串变量 v_name，用于存储从游标中获取的部门名称
    DECLARE v_name VARCHAR(50);
    -- 声明一个游标 cur，用于查询 departments 表中 parent_id 等于 p_parent_id 的记录
    DECLARE cur CURSOR FOR
        SELECT id, name FROM departments WHERE parent_id = p_parent_id;
    -- 声明一个继续处理程序，当游标没有更多数据时，将 done 置为 TRUE
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- 创建一个临时表 temp_departments，用于存储递归调用的结果
    -- 仅在该表不存在时创建，包含三个列：id、name 和 level
    CREATE 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 THEN
            LEAVE 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，层级加 1
        SET @result = recursive_departments_func(v_id, p_level + 1);
    END LOOP;

    -- 关闭游标 cur
    CLOSE cur;

    -- 函数最终返回 1
    RETURN 1;
END //

DELIMITER ;

Mysql 8 实现递归查询

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

  1. 创建示例数据

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

-- 创建表
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 及其所有下属的示例：

WITH RECURSIVE employee_hierarchy AS (
    -- 初始查询，找到CEO
    SELECT employee_id, name, manager_id
    FROM employees
    WHERE name = 'CEO'
    UNION ALL
    -- 递归部分，找到下属员工
    SELECT e.employee_id, e.name, e.manager_id
    FROM employees e
    INNER 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。
    • 初始查询部分：
      SELECT employee_id, name, manager_id
FROM employees
WHERE name = 'CEO'
      这部分找到 CEO 的记录，作为递归的起点。
    • UNION ALL 用于将初始查询结果和递归查询结果合并。
    • 递归部分：
      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）的所有上级：

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

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

  1. 组织递归查询示例
-- 假设我们有一个 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 depth
    FROM organizations
    WHERE id = 1
    UNION ALL
    -- 递归部分：选择子组织，深度加 1
    SELECT o.id, o.parent_id, o.name, o.level, oh.depth + 1
    FROM organizations o
    JOIN 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)。
-- 使用 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_id
    FROM departments
    WHERE parent_department_id IS NULL
    UNION ALL
    SELECT d.department_id, d.department_name, d.parent_department_id
    FROM departments d
    INNER JOIN department_hierarchy dh ON d.parent_department_id = dh.department_id
)
SELECT * FROM department_hierarchy;
  • 示例2：使用 CONNECT BY 和 START WITH 子句进行递归查询，以查询 id 为 1 的组织（总公司）及其所有子组织。
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 操作计算每个组织及其子组织的员工总数。
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_level
    FROM organizations o
    START WITH o.id = 1
    CONNECT 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 列表示组织的预算，并且预算可以从父组织分配给子组织。我们可以使用递归查询计算每个组织及其子组织的最终预算
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_level
    FROM organizations o
    START WITH o.id = 1
    CONNECT BY PRIOR o.id = o.parent_id
)
SELECT ba.id, ba.parent_id, ba.name, ba.original_budget,
       CASE
           WHEN ba.original_budget = 0 THEN
               NVL((LAG(ba.allocated_budget) OVER (ORDER BY ba.org_level DESC) / COUNT(*) OVER (PARTITION BY ba.parent_id)), 0)
           ELSE ba.allocated_budget
       END AS final_budget,
       ba.org_level
FROM budget_allocation ba;

SQL Server 递归查询示例

  • 支持版本：SQL Server 2005 开始支持 WITH 子句，包括递归 CTE（Common Table Expressions）。
  • 示例：假设有一个员工表 Employees，结构为 (EmployeeID, Name, ManagerID)。
WITH RECURSIVE EmployeeHierarchy AS (
    SELECT EmployeeID, Name, ManagerID
    FROM Employees
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.EmployeeID, e.Name, e.ManagerID
    FROM Employees e
    INNER 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)。
WITH RECURSIVE menu_hierarchy AS (
    SELECT menu_id, menu_name, parent_menu_id
    FROM menus
    WHERE parent_menu_id IS NULL
    UNION ALL
    SELECT m.menu_id, m.menu_name, m.parent_menu_id
    FROM menus m
    INNER JOIN menu_hierarchy mh ON m.parent_menu_id = mh.menu_id
)
SELECT * FROM menu_hierarchy;

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：/a/954810.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

接上篇基于Alertmanager 配置钉钉告警

接上篇基于Alertmanager 配置钉钉告警

Alertmanager 是一个用于处理和管理 Prometheus 警报的开源工具。它负责接收来自 Prometheus 服务器的警报，进行去重、分组、静默、抑制等操作，并通过电子邮件、PagerDuty、Slack 等多种渠道发送通知。 主要功能 去重：合并相同或相似的警报&…
阅读更多...
Qt应用之MDI（多文档设计）

Qt应用之MDI（多文档设计）

qt creator 版本6.8.0 MinGW 64bit 由此模块可以扩展成设计一个qt文本编辑器。 界面如下 部分功能展示如下 新建文件 打开文件 mdi模式、级联模式和平铺模式 界面和程序构建过程。 1.如图所需.cpp和.h文件 2.mainwindow.ui和tformdoc.ui界面布局如下 不懂什么是Action如何…
阅读更多...
软件授权管理中的软件激活向导示例

软件授权管理中的软件激活向导示例

软件激活向导示例 在软件许可中，提供许可应该是简单和安全的。这适用于想要在中央许可证服务器上创建新许可证的软件开发人员，也适用于需要在其设备上获得许可证的最终用户。如果所讨论的系统有互联网连接，或是暂时的连接，就可以…
阅读更多...
GB44495-2024 汽车整车信息安全技术要求 - V2X部分前置要求

GB44495-2024 汽车整车信息安全技术要求 - V2X部分前置要求

背景 GB 44495-2024《汽车整车信息安全技术要求》中关于V2X（车与外界通信）的部分，主要关注于通信安全要求，旨在确保车辆在与外部设备进行数据交互时的信息安全。其测试大致可分为消息层（数据无异常）、应用…
阅读更多...
phpstudy靶场搭建问题

phpstudy靶场搭建问题

前言： 靶场搭建遇到的问题，记录一下，可能是基础不牢吧，老是遇到奇奇怪怪的问题 思路： 跟着网上的搭建走一遍 内容： 目录 搭建pikachu遇到的问题 搭建pikachu遇到的问题 其实并不是第一次搭建&#x…
阅读更多...
【Excel】【VBA】双列排序：坐标从Y从大到小排列之后相同Y坐标的行再对X从小到大排列

【Excel】【VBA】双列排序：坐标从Y从大到小排列之后相同Y坐标的行再对X从小到大排列

Excel VBA 双列排序 功能概述 这段VBA代码实现了Excel中的双列排序功能，具体是： 跳过前3行表头先按C列数据从大到小排序在C列值相同的情况下，按B列从大到小排序排序时保持整行数据的完整性 流程图 #mermaid-svg-XJERemQluZlM4K8l {font-fa…
阅读更多...
Hive SQL必刷练习题：留存率问题

Hive SQL必刷练习题：留存率问题

首次登录算作当天新增，第二天也登录了算作一日留存。可以理解为，在10月1号登陆了。在10月2号也登陆了，那这个人就可以算是在1号留存 今日留存率 （今日登录且明天也登录的用户数） / 今日登录的总用户数 * 100% 解决思…
阅读更多...
C++基础入门（二）

C++基础入门（二）

目录 前言 一、重载 1.函数重载 2.运算符重载 二、构造函数 1.什么是构造函数 2.带参数的构造函数 3.使用初始化列表 4.this关键字 5.new关键字 三、析构函数 1.什么是析构函数 四、静态成员变量 1.静态成员的定义 2.静态成员变量的作用 五、继承 1.继承基本概…
阅读更多...
Redis 中 TTL 的基本知识与禁用缓存键的实现策略（Java）

Redis 中 TTL 的基本知识与禁用缓存键的实现策略（Java）

目录 前言1. 基本知识2. Java代码 前言 🤟 找工作，来万码优才：👉 #小程序://万码优才/r6rqmzDaXpYkJZF 单纯学习Redis可以看我前言的Java基本知识路线！！ 对于Java的基本知识推荐阅读： java框架…
阅读更多...
基于unity的多人家装应用的设计与实现

基于unity的多人家装应用的设计与实现

摘要 本课题根据主流家装应用存在的问题和结合了Unity3D引擎所具有的优势，在主流家装应用的基础上弥补了常见的缺憾，实现了一种新型的交互更强的家装展示系统。 本系统主要通过将家具模型资源和材质等资源导入Unity3D平台中，通过C#代码开发&a…
阅读更多...
Three.js+Vue3+Vite应用lil-GUI调试开发3D效果（三）

Three.js+Vue3+Vite应用lil-GUI调试开发3D效果（三）

前期文章中我们完成了创建第一个场景、添加轨道控制器的功能，接下来我们继续阐述其他的功能，本篇文章中主要讲述如何应用lil-GUI调试开发3D效果，在开始具体流程和步骤之前，请先查看之前的内容，因为该功能必须在前期内容…
阅读更多...
采用海豚调度器+Doris开发数仓保姆级教程（满满是踩坑干货细节，持续更新）

采用海豚调度器+Doris开发数仓保姆级教程（满满是踩坑干货细节，持续更新）

一、采用海豚调度器Doris开发平替CDH Hdfs Yarn Hive Oozie的理由。 海豚调度器Doris离线数仓方案与CDH Hive在多个方面存在显著差异，以下是对这两种方案的对比分析： 1. 架构复杂性 CDH Hive：基于Hadoop生态，组件众多&#…
阅读更多...
50.【8】BUUCTF WEB HardSql

50.【8】BUUCTF WEB HardSql

进入靶场 随便输输 上order by ????????，被过滤了,继续找其他也被过滤的关键字 #，-- -，-- 都不行，尝试其他特殊字符后发现and，union，select，空格，都被过滤了 如下 我就不知…
阅读更多...
Redis 3.2.1在Win10系统上的安装教程

Redis 3.2.1在Win10系统上的安装教程

诸神缄默不语-个人CSDN博文目录 这个文件可以跟我要，也可以从官网下载：https://github.com/MicrosoftArchive/redis/releases 这个是微软以前维护的Windows版Redis安装包，如果想要比较新的版本可以从别人维护的项目里下（https://…
阅读更多...
mac配置 iTerm2 使用lrzsz与服务器传输文件

mac配置 iTerm2 使用lrzsz与服务器传输文件

mac配置 1. 安装支持rz和sz命令的lrzsz brew install lrzsz2. 下载iterm2-send-zmodem.sh和iterm2-recv-zmodem.sh两个脚本 # 克隆仓库 git clone https://github.com/aikuyun/iterm2-zmodem ~/iterm2-zmodem# 进入到仓库目录 cd ~/iterm2-zmodem# 设置脚本文件可执行权限 c…
阅读更多...
9.7 visual studio 搭建yolov10的onnx的预测（c++）

9.7 visual studio 搭建yolov10的onnx的预测（c++）

1.环境配置 在进行onnx预测前，需要搭建的环境如下: 1.opencv环境的配置，可参考博客:9.2 c搭建opencv环境-CSDN博客 2.libtorch环境的配置，可参考博客：9.4 visualStudio 2022 配置 cuda 和 torch (c)-CSDN博客 3.cuda环境的配置…
阅读更多...
YOLOv8从菜鸟到精通(二):YOLOv8数据标注以及模型训练

YOLOv8从菜鸟到精通(二):YOLOv8数据标注以及模型训练

数据标注 前期准备 先打开Anaconda Navigator，点击Environment，再点击new(new是我下载anaconda的文件夹名称)，然后点击创建 点击绿色按钮，并点击Open Terminal 输入labelimg便可打开它,labelimg是图像标注工具，在上篇…
阅读更多...
STM32-keil安装时遇到的一些问题以及解决方案

STM32-keil安装时遇到的一些问题以及解决方案

前言： 本人项目需要使用到STM32,故需配置keil 5，在配置时遇到了以下问题，并找到相应的解决方案，希望能够为遇到相同问题的道友提供一些解决思路 1、提示缺少（missing）version 5编译器 step1：找…
阅读更多...
C语言结构体漫谈：从平凡中见不平凡

C语言结构体漫谈：从平凡中见不平凡

大家好，这里是小编的博客频道 小编的博客：就爱学编程 很高兴在CSDN这个大家庭与大家相识，希望能在这里与大家共同进步，共同收获更好的自己！！！ 本文目录 引言正文《1》 结构体的两种声明一、结构…
阅读更多...
LabVIEW与WPS文件格式的兼容性

LabVIEW与WPS文件格式的兼容性

LabVIEW 本身并不原生支持将文件直接保存为 WPS 格式（如 WPS 文档或表格）。然而，可以通过几种间接的方式实现这一目标，确保您能将 LabVIEW 中的数据或报告转换为 WPS 可兼容的格式。以下是几种常见的解决方案： ​ 导出…
阅读更多...
最新文章

Copyright @ 2022~2023