mysql给每级节点编号_MySql 获取当前节点及递归所有上级节点

-- MySql 获取当前节点及递归所有上级节点

-- 参数说明：resultField：查询返回字段，idd 要查询的资源ID值，idFieldName ID字段名，parentIdFieldName 上级ID字段名，tableName 表名，isContainMySelf 是否包含自己

DROP procedure IF EXISTS pro_getParentList;

create procedure pro_getParentList(in resultField varchar(4000),in myid varchar(100),in idFieldName varchar(100),in parentIdFieldName varchar(100),in tableName varchar(100),isContainMySelf int)

begin

drop table if exists tmpParent;

CREATE TABLE tmpParent(temId varchar(100)) ;

set @tempParentId=myid;

set @myDoflag=1;

WHILE @myDoflag>0 do

set @ssss = CONCAT(‘ select ‘, parentIdFieldName, ‘, count(*) INTO @tempParentId,@myDoflag from ‘,tableName ,‘ where ‘,idFieldName,‘=‘,‘‘‘‘,@tempParentId,‘‘‘‘);

prepare strsql from @ssss;

execute strsql;

deallocate prepare strsql;

if(@myDoflag>0) THEN

set @insertSql = CONCAT(‘INSERT into tmpParent(temId) values (‘,"‘",@tempParentId,"‘",‘)‘);

prepare preSql1 from @insertSql;

execute preSql1;

deallocate prepare preSql1;

end if;

end WHILE;

IF (isContainMySelf=1) THEN

INSERT into tmpParent(temId) values (myid);

END IF;

SET @fieldSql=concat(‘ SELECT ‘,resultField);

set @strsql = CONCAT(@fieldSql,‘ from ‘, tableName,‘ s ‘,‘ inner join tmpParent t on t.temId=s.‘,idFieldName,‘ order by s.‘,idFieldName,‘ asc‘);

prepare preSql from @strsql;

execute preSql;

end

MySql 获取当前节点及递归所有上级节点

标签：rom   pre   包含   res   sel   dna   mysq   har   eal

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉

本文系统来源：http://www.cnblogs.com/f2flow/p/6003221.html