mysql获取某个节点所有的上级节点

女爷i 2022-03-11 06:38 1558阅读 0赞

# 表结构 #

<table> 
 <thead> 
  <tr> 
   <th>节点编号</th> 
   <th>上级节点</th> 
   <th>节点名称</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>id</td> 
   <td>parend_id</td> 
   <td>name</td> 
  </tr> 
 </tbody> 
</table>

# 最终需要查询的效果 #

![mysql 递归查询][mysql]

## 先定义 一个 递归查询上级 的 函数 ##

DELIMITER $$
    USE `1609a`$$
    DROP FUNCTION IF EXISTS `getParents`$$
    CREATE DEFINER=`root`@`%` FUNCTION `getParents`(self_Id VARCHAR(40)) 
    RETURNS VARCHAR(1000) CHARSET utf8  ##定义返回值类型 varchar(4000)
    BEGIN                    ##函数开始
        DECLARE sTemp text(40000);    ##定义一个varchar类型的参数
        DECLARE sTempChd text(40000);    ##定义一个varchar类型的参数
    		DECLARE counts int;
        SET sTemp = "";            ##给sTmp赋值
        SET sTempChd =self_Id;
    		set counts  =  0;
       WHILE sTempChd IS NOT NULL DO   ##循环体
    			SET sTemp = CONCAT(sTempChd,"/",sTemp);  
    				
    				##拼接sTemp 
    			select count(*) INTO  counts   FROM tb_item_cat   WHERE id = sTempChd;
    				IF (counts=0)THEN  
    					set sTempChd = NULL ;
    			else 
    				SELECT parent_Id INTO sTempChd  FROM tb_item_cat   WHERE id = sTempChd;
    			END IF;	
            ##根据父节点，查询出该父节点下的所有子节点的id，支持多级查询
            END WHILE;  
          RETURN left(sTemp,length(sTemp)-1);
    END$$
    DELIMITER

查询sql
     select t1.id,t1.name,t2.name ,getParents(t1.id) as path from tb_item_cat t1 left join  tb_item_cat t2 on t1.parent_id = t2.id

## sql 查询 的结果 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hpZ2hlcl9nYW8_size_16_color_FFFFFF_t_70]

[mysql]: /images/20220311/70a3afcc7ff843bfbb201e675b1b3a93.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hpZ2hlcl9nYW8_size_16_color_FFFFFF_t_70]: /images/20220311/45d16003faed43b58ae266c2e2466a43.png