使用MYSQL实现Oracle的Start with...Connect By递归树查询

创建表nodelist：

CREATE TABLE nodelist(  id INT PRIMARY KEY,            //本节点ID。  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);  

创建函数getChildList()：

CREATE FUNCTION `getChildList`(rootId INT) //rootId为你要查询的节点。RETURNS VARCHAR(1000)  BEGIN DECLARE pTemp VARCHAR(1000);  DECLARE cTemp VARCHAR(1000);  //两个临时变量SET pTemp = '$';  SET cTemp =cast(rootId as CHAR);  //把rootId强制转换为字符。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; // FIND_IN_SET(str,strlist)的方法网上大把不解释。END WHILE;  RETURN pTemp;  END 

执行方法getChildList(1)：

SELECT getChildList(1);  

查询节点为“3”下的所有节点：

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