存储过程查询表中父子级数据

分享知识 传递快乐

 

 

根据父级节点ID查询子节点

DROP FUNCTION IF EXISTS `getChildList`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` FUNCTION `getChildList`(
	`paramId` INT(11)
) RETURNS varchar(255) CHARSET utf8
BEGIN 
	DECLARE str VARCHAR(255) ; 
	DECLARE ids CHAR(255) ; 

	SET str = ""; 
	SET ids = cast(paramId AS CHAR);

	WHILE ids is not null DO 
		SET str = IF(str != "", concat(str,',',ids), concat(ids));
		SELECT group_concat(id) INTO ids FROM resource where FIND_IN_SET(pid,ids) > 0; 
	END WHILE; 
	RETURN str; 
END
;;
DELIMITER ;

 

根据子级节点ID查询父级节点ID

DROP FUNCTION IF EXISTS `getParentList`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` FUNCTION `getParentList`(`paramId` int) RETURNS char(255) CHARSET utf8
BEGIN
DECLARE fid INT DEFAULT 1;
DECLARE str CHAR (255) DEFAULT paramId;

while (paramid >0) do
	set fid = (select pid from resource where id=paramid);
	if (fid >0) then
		set str = concat(str,",",fid);
		set paramid = fid;
	else
		set paramid = fid;
	end if;
end while;
return str;
END
;;
DELIMITER ;

 

 

 

 

 

 

——————————————
相互学习，共同进步
如不妥之处请留言指正