mysql多表树形结构递归实现_Mysql 树形结构表递归父级节点函数方法

地区表

通过当前节点编号查询父级所有编号集合，通过逗号进行分隔(倒序，包含自身编号)

-- 通过当前节点编号查询父级所有编号集合，通过逗号进行分隔(倒序，包含自身编号)

-- DROP FUNCTION `getDistrictParentIds`;

CREATE FUNCTION `getDistrictParentIds`(nodeId varchar(100))

RETURNS varchar(1000)

BEGIN

DECLARE pid varchar(100) default '';

DECLARE ids varchar(1000) default nodeId;

WHILE nodeId is not null do

SET pid =(SELECT district_pid FROM district WHERE district_id = nodeId);

IF pid is not null THEN

SET ids = concat(ids, ',', pid);

SET nodeId = pid;

ELSE

SET nodeId = pid;

END IF;

END WHILE;

return ids;

END

通过当前节点编号查询父级所有名称集合，通过逗号进行分隔(正序，包含自身名称)

-- 通过当前节点编号查询父级所有名称集合，通过逗号进行分隔(正序，包含自身名称)

-- DROP FUNCTION `getDistrictParentNames`;

CREATE FUNCTION `getDistrictParentNames`(nodeId varchar(100))

RETURNS varchar(1000)

BEGIN

DECLARE pid varchar(100) default '';

DECLARE ids varchar(1000) default nodeId;

WHILE nodeId is not null do

SET pid =(SELECT district_pid FROM district WHERE district_id = nodeId);

IF pid is not null THEN

SET ids = concat(ids, ',', pid);

SET nodeId = pid;

ELSE

SET nodeId = pid;

END IF;

END WHILE;

return (select group_concat(district_name) from district where FIND_IN_SET(district_id,ids) order by district_id);

END

行业表

通过当前节点编号查询父级所有编号集合，通过逗号进行分隔(倒序，包含自身编号)

-- 通过当前节点编号查询父级所有编号集合，通过逗号进行分隔(倒序，包含自身编号)

-- DROP FUNCTION `getIndustryParentIds`;

CREATE FUNCTION `getIndustryParentIds`(nodeId varchar(100))

RETURNS varchar(1000)

BEGIN

DECLARE pid varchar(100) default '';

DECLARE ids varchar(1000) default nodeId;

WHILE nodeId is not null do

SET pid =(SELECT industry_pid FROM industry WHERE industry_id = nodeId);

IF pid is not null THEN

SET ids = concat(ids, ',', pid);

SET nodeId = pid;

ELSE

SET nodeId = pid;

END IF;

END WHILE;

return ids;

END

通过当前节点编号查询父级所有名称集合，通过逗号进行分隔(正序，包含自身名称)

-- 通过当前节点编号查询父级所有名称集合，通过逗号进行分隔(正序，包含自身名称)

-- DROP FUNCTION `getIndustryParentNames`;

CREATE FUNCTION `getIndustryParentNames`(nodeId varchar(100))

RETURNS varchar(1000)

BEGIN

DECLARE pid varchar(100) default '';

DECLARE ids varchar(1000) default nodeId;

WHILE nodeId is not null do

SET pid =(SELECT industry_pid FROM industry WHERE industry_id = nodeId);

IF pid is not null THEN

SET ids = concat(ids, ',', pid);

SET nodeId = pid;

ELSE

SET nodeId = pid;

END IF;

END WHILE;

return (select group_concat(industry_name) from industry where FIND_IN_SET(industry_id,ids) order by industry_id);

END