首页 > 代码库 > 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 获取当前节点及递归所有上级节点