mysql 有类似于oracle START WITH的实现吗?

Question

查了一下资料 貌似都是定制化的(针对某一张表的具体实现)

因为项目中有好几张表要用到Start with...Connect By

请问有谁有过相关经验 能具体写一个模板吗?

Answer 1

MySQL 中也有类似于 Oracle 的 START WITH 的实现，这个实现叫做 WITH RECURSIVE。WITH RECURSIVE 语句可以用来查询树形结构的数据，例如组织架构、分类目录等。

WITH RECURSIVE cte_name (column_list) AS (
    initial_query
    UNION ALL
    recursive_query
)
SELECT *
FROM cte_name;

Answer 2

CREATE TABLE nodelist(  
 id INT PRIMARY KEY,           
 nodename VARCHAR(20),         
 pid INT                       
); 

Insert INTO nodelist VALUES(1,'A',null);  
Insert INTO nodelist VALUES(2,'B',1);  
Insert INTO nodelist VALUES(3,'C',1);  
Insert INTO nodelist VALUES(4,'D',2);  
Insert INTO nodelist VALUES(5,'E',3);  
Insert INTO nodelist VALUES(6,'F',3);  
Insert INTO nodelist VALUES(7,'G',5);  
Insert INTO nodelist VALUES(8,'H',7);  
Insert INTO nodelist VALUES(9,'I',8);  
Insert INTO nodelist VALUES(10,'J',8);  

CREATE FUNCTION getChildList(rootId INT) 
     RETURNS VARCHAR(1000)  
     BEGIN 
       DECLARE pTemp VARCHAR(1000);  
       DECLARE cTemp VARCHAR(1000);  
      
       SET pTemp = '$';  
       SET cTemp =cast(rootId as CHAR); 
      
       WHILE cTemp is not null DO  
         SET pTemp = concat(pTemp,',',cTemp);  
         SELECT group_concat(id) INTO cTemp FROM nodelist   
         WHERE FIND_IN_SET(pid,cTemp)>0;
       END WHILE;  
       RETURN pTemp;  
     END 

SELECT getChildList(3);  

SELECT * FROM nodelist WHERE FIND_IN_SET(id, getChildList(3));