mysql 递归查询父节点及子节点

查询父节点

Drop FUNCTION if EXISTS `getParentList`;
CREATE FUNCTION `getParentList`(rootId varchar(100))   
RETURNS varchar(1000)   
BEGIN   
DECLARE fid varchar(100) default '';   
DECLARE str varchar(1000) default rootId;   
  
WHILE rootId is not null  do   
    SET fid =(SELECT cuntongncxx.NCXX_DS FROM cuntongncxx WHERE ID = rootId);   
    IF fid is not null THEN   
        SET str = concat(str, ',', fid);   
        SET rootId = fid;   
    ELSE   
        SET rootId = fid;   
    END IF;   
END WHILE;   
return str;  
END  

 SELECT getParentList('JXW-001-002-001');

查询结果：

查询子节点 （不包含本节点）

DROP FUNCTION IF EXISTS `getChildList`;
CREATE FUNCTION `getChildList`(rootId varchar(2000))    
RETURNS varchar(2000)
BEGIN   
DECLARE cid varchar(2000) default rootId;   
DECLARE str varchar(2000) default '$';   
  
WHILE cid is not null  do  
    SELECT GROUP_CONCAT(cuntongncxx.ID)into cid FROM cuntongncxx WHERE FIND_IN_SET(cuntongncxx.NCXX_DS,cid)>0;   
IF  cid is not NULL
THEN
set str=CONCAT(str,',',cid);
end IF;
END WHILE; 
return str;  
END  

SELECT getChildList('JXW000001');

查询子节点 （包含本节点）

DROP FUNCTION IF EXISTS `getChildList`;
CREATE FUNCTION `getChildList`(rootId varchar(2000))    
RETURNS varchar(2000)
BEGIN   
DECLARE cid varchar(2000) default rootId;   
DECLARE str varchar(2000) default '$';   
  
WHILE cid is not null  do  
    set str=CONCAT(str,',',cid);
    SELECT GROUP_CONCAT(cuntongncxx.ID)into cid FROM cuntongncxx WHERE FIND_IN_SET(cuntongncxx.NCXX_DS,cid)>0;   
END WHILE; 
return str;  
END  

DROP FUNCTION IF EXISTS `getChildList`;
CREATE FUNCTION `getChildList`(rootId varchar(2000))    
RETURNS varchar(2000)
BEGIN   
DECLARE cid varchar(2000) default rootId;   
DECLARE str varchar(2000) default '$';   
  
WHILE cid is not null  do  
set str=CONCAT(str,',',cid);
    SELECT GROUP_CONCAT(cuntongncxx.ID)into cid FROM cuntongncxx WHERE FIND_IN_SET(cuntongncxx.NCXX_DS,cid)>0;   
END WHILE; 


return str;  
END