MySQL递归查询组织机构

谁践踏了优雅 2023-10-10 11:01 55阅读 0赞

#### MySQL递归查询组织机构 ####

*   *  一、创建表和添加测试数据
     *   *  1.建表
         *  2.测试数据
     *  二、编写函数（根据父id递归查询所有子节点）
     *   *  1.创建函数
         *  2.调用函数查询

### 一、创建表和添加测试数据 ###

#### 1.建表 ####

*  建表语句：

DROP TABLE IF EXISTS recursion_org;
    CREATE TABLE recursion_org (
    id BIGINT(8) NOT NULL AUTO_INCREMENT,
    org_name VARCHAR(50) NOT NULL,
    org_level INT(4) NOT NULL DEFAULT '0',
    org_parent_id BIGINT(8) NOT NULL DEFAULT '0',
    PRIMARY KEY (id),
    UNIQUE KEY unique_org_name (org_name)
    ) ENGINE=INNODB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;

#### 2.测试数据 ####

*  添加数据：

-- 插入数据
    INSERT INTO recursion_org VALUES ('1', '北信源', '1', '0');
    INSERT INTO recursion_org VALUES ('2', '北京', '2', '1');
    INSERT INTO recursion_org VALUES ('3', '南京', '2', '1');
    INSERT INTO recursion_org VALUES ('4', '武汉', '2', '1');
    INSERT INTO recursion_org VALUES ('5', '上海', '2', '1');
    INSERT INTO recursion_org VALUES ('6', '北京研发中心', '3', '2');
    INSERT INTO recursion_org VALUES ('7', '南京研发中心', '3', '3');
    INSERT INTO recursion_org VALUES ('8', '武汉研发中心', '3', '4');
    INSERT INTO recursion_org VALUES ('9', '上海研发中心', '3', '5');
    INSERT INTO recursion_org VALUES ('10', '北京EMM项目组', '4', '6');
    INSERT INTO recursion_org VALUES ('11', '北京linkdd项目组', '4', '6');
    INSERT INTO recursion_org VALUES ('12', '南京EMM项目组', '4', '7');
    INSERT INTO recursion_org VALUES ('13', '南京linkdd项目组', '4', '7');
    INSERT INTO recursion_org VALUES ('14', '武汉EMM项目组', '4', '8');
    INSERT INTO recursion_org VALUES ('15', '武汉linkdd项目组', '4', '8');
    INSERT INTO recursion_org VALUES ('16', '上海EMM项目组', '4', '9');
    INSERT INTO recursion_org VALUES ('17', '上海linkdd项目组', '4', '9');

### 二、编写函数（根据父id递归查询所有子节点） ###

#### 1.创建函数 ####

DELIMITER -- 声明分隔符
    CREATE FUNCTION getChildrenOrg_one(rootId VARCHAR(20)) 
    RETURNS VARCHAR (1000)-- 声明返回值类型
    BEGIN 
        DECLARE pTemp VARCHAR(1000);            
        DECLARE cTemp VARCHAR(1000);      -- 节点ID(临时变量)
    
        SET pTemp = '$';  -- 父节点id
        SET cTemp =CAST(rootId AS CHAR);  -- 把rootId强制转换为字符。
    
        WHILE cTemp IS NOT NULL DO  
           SET pTemp = CONCAT(pTemp,',',cTemp);  -- 把所有节点连接成字符串。
           SELECT GROUP_CONCAT(id) INTO cTemp FROM recursion_org  -- 'recursion_org'修改成自己数据库中所需要递归查询的表名  
           WHERE FIND_IN_SET(org_parent_id,cTemp)>0; -- 这里的'org_parent_id' 需要填写自己表中对应的父id字段
        END WHILE;  
        RETURN pTemp;  
    END //
    DELIMITER ;

#### 2.调用函数查询 ####

*  方式一：

SELECT * FROM recursion_org WHERE FIND_IN_SET(id,getChildrenOrg_one(2))

*  结果一：  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXppY2M_size_16_color_FFFFFF_t_70]
 *  方式二：

SELECT
       *
    FROM 
        recursion_org T,
        (SELECT @DATAS := getChildrenOrg_one (4)) X 
    WHERE FIND_IN_SET (ID, @DATAS)

*  结果二：  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXppY2M_size_16_color_FFFFFF_t_70 1]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXppY2M_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200425224650487.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXppY2M=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXppY2M_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200425224953612.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXppY2M=,size_16,color_FFFFFF,t_70