这里写自定义目录标题

• 最近的工作中遇到，需要将Oracel库转到Mysql库，有些语法不同，需要重写对应的SQL语句，这次遇到的是start with 递归查询的修改
• • 第一种方案，MySQL8.0以上版本直接使用WITH RECURSIVE语句
  • 第二种方案，存储过程，8.0以上和以下都可以支持。
  • 第三种嵌套查询
  • 第四种自连接查询
  • 第五种使用自连接和变量查询
  • 第六种使用游标
  • Oracle中的递归查询，使用 CONNECT BY 和 START WITH 子句来实现递归查询

最近的工作中遇到，需要将Oracel库转到Mysql库，有些语法不同，需要重写对应的SQL语句，这次遇到的是start with 递归查询的修改

Oracel中有Start 这个语法支持，但是MySQL 8.0版本以上有WITH RECURSIVE语法支持；8.0以下没有特定的语法支持，只能是通过存储过程，函数，SQL语句曲线救国实现ORACEL中同样的效果。

第一种方案，MySQL8.0以上版本直接使用WITH RECURSIVE语句

WITH RECURSIVE cte_name (column_name, ...) AS (    SELECT initial_query    UNION [ALL]    SELECT recursive_query FROM cte_name)SELECT * FROM cte_name;

*上面的代码中，cte_name是递归查询的名称，column_name是列名，initial_query是初始查询语句，recursive_query是递归查询语句。WITH RECURSIVE语句中的UNION ALL是用于连接初始查询和递归查询的。*结合下面的案例可以更好地理解：

WITH RECURSIVE org_hierarchy(id, name, parent_id, level) AS (    SELECT id, name, parent_id, 1    FROM departments    WHERE parent_id IS NULL    UNION ALL    SELECT d.id, d.name, d.parent_id, oh.level + 1    FROM departments d    JOIN org_hierarchy oh ON oh.id = d.parent_id)SELECT * FROM org_hierarchy ORDER BY level, id;

上面的这段SQL主要实现的功能是，初始查询语句是从顶层部门开始查询，即parent_id为NULL的部门，递归查询语句是查询与上一级部门有关联的下一级部门，同时需要把查询结果的level加1。
##其次 使用存储过程

第二种方案，存储过程，8.0以上和以下都可以支持。

DELIMITER //CREATE PROCEDURE org_hierarchy(IN parent_id INT, IN level INT)BEGINDECLARE done INT DEFAULT FALSE;DECLARE cur_id INT;DECLARE cur_name VARCHAR(255);DECLARE cur_level INT;DECLARE cur_parent_id INT;DECLARE cur_dept CURSOR FOR SELECT id, name, parent_id FROM departments WHERE parent_id = parent_id;DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;OPEN cur_dept;dept_loop: LOOP    FETCH cur_dept INTO cur_id, cur_name, cur_parent_id;    IF done THEN        LEAVE dept_loop;    END IF;    INSERT INTO org_hierarchy(id, name, parent_id, level) VALUES(cur_id, cur_name, cur_parent_id, level);    CALL org_hierarchy(cur_id, level + 1);END LOOP;CLOSE cur_dept;END//DELIMITER ;

在存储过程中，首先声明了一些变量，包括done（表示循环是否完成）、cur_id、cur_name、cur_level、cur_parent_id等。然后使用CURSOR语句声明了一个游标，用于查询当前部门的下一级部门。使用FETCH语句获取游标的结果集，如果没有更多的结果，则设置done变量为TRUE，退出循环。在循环中，将当前部门的信息插入到org_hierarchy表中，并调用存储过程本身，递归查询下一级部门。

DELIMITER //CREATE PROCEDURE find_children (IN parent_id INT)BEGIN    DECLARE child_id INT;    DECLARE done INT DEFAULT FALSE;    DECLARE cur CURSOR FOR SELECT id FROM departments WHERE parent_id = parent_id;    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;    DECLARE children CURSOR FOR SELECT id, name FROM departments WHERE parent_id = parent_id;        CREATE TEMPORARY TABLE IF NOT EXISTS temp_children (        id INT,        name VARCHAR(255)    );        OPEN cur;    FETCH cur INTO child_id;        WHILE NOT done DO        CALL find_children(child_id);        OPEN children;        LOOP            FETCH children INTO child_id, name;            IF done THEN                LEAVE LOOP;            END IF;            INSERT INTO temp_children (id, name) VALUES (child_id, name);        END LOOP;        CLOSE children;        FETCH cur INTO child_id;    END WHILE;    CLOSE cur;        SELECT * FROM temp_children WHERE 1;        DROP TEMPORARY TABLE IF EXISTS temp_children;END //

CALL find_children(1);

以上是使用存储过程实现SQL递归查询的示例。虽然使用存储过程可以实现复杂的递归查询逻辑，但是需要编写较为复杂的存储过程，维护和调试也相对麻烦。因此，建议使用WITH RECURSIVE语句实现SQL递归查询，如果查询过程非常复杂，则可以考虑使用存储过程。

第三种嵌套查询

CREATE TABLE departments (    id INT,    name VARCHAR(255),    parent_id INT);

SELECT *FROM departmentsWHERE parent_id IN (    SELECT id FROM departments WHERE parent_id = 1    UNION    SELECT parent_id FROM departments WHERE parent_id IS NOT NULL AND parent_id != id);

SQL语句使用了嵌套查询，先查询parent_id为1的部门，然后查询parent_id等于该部门id的所有子部门，直到所有子部门都被查询完毕

第四种自连接查询

自连接查询可以通过在同一张表中使用别名来实现递归查询。例如，假设有一个包含组织机构和部门的表。

CREATE TABLE departments (    id INT,    name VARCHAR(255),    parent_id INT);

SELECT d2.*FROM departments d1INNER JOIN departments d2 ON d2.parent_id = d1.idWHERE d1.parent_id IS NULL;

以上SQL语句使用了自连接查询，通过连接同一张表并使用别名来查询所有子部门。首先，查询parent_id为NULL的部门作为组织机构的起点；然后，使用INNER JOIN连接部门表并指定连接条件为d2.parent_id = d1.id，即连接当前部门和其子部门；最后，使用WHERE子句指定终止条件，即d1.parent_id IS NULL，即只查询组织机构下的部门，不包含其他层级的部门。

第五种使用自连接和变量查询

SELECT *FROM (  SELECT    t1.id,    t1.name,    t1.parent_id,    @pv := CONCAT_WS(',', t1.id, @pv) AS ancestors  FROM departments t1  JOIN (SELECT @pv := '4') tmp  WHERE t1.id = @pv OR FIND_IN_SET(t1.parent_id, @pv)) t2;

SQL语句使用了一个自连接和一个MySQL变量@pv，@pv用于保存当前节点的祖先节点ID，初始值设置为要查询的部门ID。SQL语句的执行过程如下：
对departments表进行自连接，获取当前节点以及其父节点的信息，并将当前节点的ID和父节点的ID拼接成一个以逗号分隔的字符串，保存到变量@pv中。
在自连接的结果中，查询当前节点的ID等于变量@pv的部门记录，或者当前节点的父节点ID在变量@pv字符串中出现的部门记录。
对查询结果中的每一条记录，都包含了当前节点以及其所有祖先节点的信息。
需要注意的是，这种方式也需要手动维护变量@pv的更新，而且在查询的时候需要使用FIND_IN_SET函数，效率可能不够高。因此，如果使用MySQL 8.0版本及以上的版本，建议使用WITH RECURSIVE语法来实现递归查询，更加直观和易于维护

第六种使用游标

SQL语句首先定义了一个变量@department_id，表示要查询的部门ID，然后使用临时表recursive_departments来存储递归查询的结果，包括部门的ID、名称、父节点的ID、层级、以及路径。SQL语句的执行过程如下：
创建临时表recursive_departments，将指定部门ID的记录插入到表中，作为初始的查询结果。
使用递归的方式，查询所有父节点ID等于当前查询结果中节点ID的部门记录，并将这些记录插入到recursive_departments表中。
在递归查询的过程中，使用UNION ALL将所有查询结果合并到recursive_departments表中，最终得到所有与指定部门ID有关的部门记录。

SET @department_id := 4;DROP TEMPORARY TABLE IF EXISTS recursive_departments;CREATE TEMPORARY TABLE recursive_departments (  id INT NOT NULL PRIMARY KEY,  name VARCHAR(255),  parent_id INT,  level INT,  path VARCHAR(255));INSERT INTO recursive_departmentsSELECT  id,  name,  parent_id,  0 AS level,  CAST(id AS CHAR(255)) AS pathFROM departmentsWHERE id = @department_idUNION ALLSELECT  t1.id,  t1.name,  t1.parent_id,  t2.level + 1 AS level,  CONCAT_WS(',', t1.id, t2.path) AS pathFROM departments t1JOIN recursive_departments t2 ON t1.parent_id = t2.id;SELECT * FROM recursive_departments;

Oracle中的递归查询，使用 CONNECT BY 和 START WITH 子句来实现递归查询

CREATE TABLE departments (  id NUMBER PRIMARY KEY,  name VARCHAR2(50),  parent_id NUMBER);INSERT INTO departments VALUES (1, 'Department 1', NULL);INSERT INTO departments VALUES (2, 'Department 2', 1);INSERT INTO departments VALUES (3, 'Department 3', 2);INSERT INTO departments VALUES (4, 'Department 4', 2);INSERT INTO departments VALUES (5, 'Department 5', 1);INSERT INTO departments VALUES (6, 'Department 6', 5);

要查询部门 4 的所有父节点，可以使用以下 SQL 语句

SELECT id, name, parent_idFROM departmentsSTART WITH id = 4CONNECT BY PRIOR parent_id = id;

START WITH id = 4 表示从 ID 为 4 的部门开始查询，CONNECT BY PRIOR parent_id = id 表示按照 parent_id 和 id 字段建立连接关系，并进行递归查询。执行以上 SQL 语句，将返回以下结果

ID  NAME          PARENT_ID--  ------------  ---------4   Department 4  22   Department 2  11   Department 1  NULL

特别提醒：使用 CONNECT BY 和 START WITH 子句进行递归查询时，需要注意循环递归的情况，否则可能会导致死循环。如果数据中存在循环递归的情况，可以使用 CONNECT_BY_ISCYCLE 伪列来判断是否存在循环递归

来源地址：https://blog.csdn.net/m0_55106953/article/details/130311496