这里写自定义目录标题
- 最近的工作中遇到，需要将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_queryUNION [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, 1FROM departmentsWHERE parent_id IS NULLUNION ALLSELECT d.id, d.name, d.parent_id, oh.level + 1FROM departments dJOIN 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: LOOPFETCH cur_dept INTO cur_id, cur_name, cur_parent_id;IF done THENLEAVE 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)BEGINDECLARE 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 DOCALL find_children(child_id);OPEN children;LOOPFETCH children INTO child_id, name;IF done THENLEAVE 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 = 1UNIONSELECT 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 (SELECTt1.id,t1.name,t1.parent_id,@pv := CONCAT_WS(',', t1.id, @pv) AS ancestorsFROM departments t1JOIN (SELECT @pv := '4') tmpWHERE 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_departmentsSELECTid,name,parent_id,0 AS level,CAST(id AS CHAR(255)) AS pathFROM departmentsWHERE id = @department_idUNION ALLSELECTt1.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 语句，将返回以下结果
IDNAMEPARENT_ID-----------------------4 Department 422 Department 211 Department 1NULL特别提醒：使用 CONNECT BY 和 START WITH 子句进行递归查询时，需要注意循环递归的情况，否则可能会导致死循环。如果数据中存在循环递归的情况，可以使用 CONNECT_BY_ISCYCLE 伪列来判断是否存在循环递归