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

Question

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

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

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

Answer 1

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));