作者:季孙意如 | 来源:互联网 | 2023-08-01 13:10
Mysql-获取子结点所有父结点
分享一下mysql中获取子结点所有父结点,原始数据如下:
注意观察,根节点父结点为0,这是循环结束条件,下面看过程
DELIMITER $$
USE `usmsc` $$
DROP FUNCTION IF EXISTS `getParents` $$
CREATE DEFINER=`root`@`%` FUNCTION `getParents`(self_Id VARCHAR(40))
RETURNS VARCHAR(1000) CHARSET utf8
BEGIN DECLARE sTemp text(40000); DECLARE sTempName text(40000); DECLARE sTempChd text(40000); DECLARE counts int;SET sTemp = ""; SET sTempChd =self_Id; SET sTempName= ""; SET counts = 0;WHILE sTempChd !=0 DO SELECT precinct_name INTO sTempName FROM usmsc.t_cfg_precinct WHERE precinct_id = sTempChd;SET sTemp = CONCAT(sTempName,"/",sTemp);select count(*) INTO counts FROM usmsc.t_cfg_precinct WHERE precinct_id = sTempChd;IF (counts=0) THENset sTempChd = NULL ;elseSELECT up_precinct_id INTO sTempChd FROM usmsc.t_cfg_precinct WHERE precinct_id = sTempChd;END IF;END WHILE;RETURN left(sTemp,length(sTemp)-1);
END$$
DELIMITER
select t1.precinct_id,t1.precinct_name,t2.precinct_name,getParents(t1.precinct_id) as path from usmsc.t_cfg_precinct t1 left join usmsc.t_cfg_precinct t2 on t1.up_precinct_id = t2.precinct_id;
其实自己拿一个例子就很容易理解
1003–>父结点1002
1002–>父结点1001
1001–>父结点0(根节点)
套用上述过程即可验证,当然也可以按照实际随机应变