MySQL递归查询组织机构

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

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))

• 结果一：
  
• 方式二：

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

• 结果二：