mysql递归查询组织机构

转载自：https://blog.csdn.net/moyanxuan_1993_2_24/article/details/52790314

父子查询： 根据父 id 查询下面所有子节点数据；子父查询： 根据子 id 查询上面所有父节点数据

创建表

DROP TABLE IF EXISTS vrv_org_tab;
CREATE TABLE vrv_org_tab (
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;

添加数据

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

执行 select * from vrv_org_tab;

根据父id递归查询所有子节点

创建函数

create function getChildrenOrg(orgid INT)
returns varchar(4000)
BEGIN
DECLARE oTemp VARCHAR(4000);
DECLARE oTempChild VARCHAR(4000);

SET oTemp = '';
SET oTempChild = CAST(orgid AS CHAR);

WHILE oTempChild IS NOT NULL
DO
SET oTemp = CONCAT(oTemp,',',oTempChild);
SELECT GROUP_CONCAT(id) INTO oTempChild FROM vrv_org_tab WHERE FIND_IN_SET(org_parent_id,oTempChild) > 0;
END WHILE;
RETURN oTemp;
END

根据函数查询

根据子id递归查询所有父节点

SELECT id,org_name,org_level,org_parent_id
	FROM ( 
		SELECT 
				@r AS _id, 
				(SELECT @r := org_parent_id FROM vrv_org_tab WHERE id = _id) AS parent_id, 
				 @l := @l + 1 AS lvl 
		FROM 
				(SELECT @r := 10000, @l := 0) vars, 
				vrv_org_tab h 
		WHERE @r <> 0) T1 
	JOIN vrv_org_tab T2 
	ON T1._id = T2.id
ORDER BY id;

执行sql

根据组织机构名称模糊查询所有父节点

创建函数

CREATE FUNCTION getParentOrgByOrgName(orgName VARCHAR(20))
RETURNS VARCHAR(4000)
BEGIN
	DECLARE sPid VARCHAR(1000);
	DECLARE sPidTemp VARCHAR(1000);
	DECLARE pid VARCHAR(1000);
	DECLARE count INT DEFAULT 0;
	DECLARE allpid VARCHAR(4000);
	
	SET sPidTemp = '';
	SELECT GROUP_CONCAT(DISTINCT(CAST(id AS CHAR))) INTO sPid 
	FROM vrv_org_tab WHERE org_name LIKE CONCAT('%',orgName,'%');
	
	SET allpid = '';
WHILE count = 0
DO
IF sPid IS NULL THEN
SET allpid = '-1';
SET count = 1;
ELSE
	SET pid = SUBSTRING_INDEX(sPid,',',1);
	SET sPidTemp = CONCAT(sPidTemp,',',pid);
	IF LENGTH(pid) = LENGTH(sPid) THEN
		SET count = 1;
		SET sPid = SUBSTRING(sPid FROM LENGTH(SUBSTRING_INDEX(sPid,',',1)) FOR LENGTH(sPid)+1);
	ELSE
		SET sPid = SUBSTRING(sPid FROM LENGTH(SUBSTRING_INDEX(sPid,',',1))+2 FOR LENGTH(sPid)+1);
	END IF;
	SELECT GROUP_CONCAT(CAST(id AS CHAR)) INTO sPidTemp
			FROM ( 
					SELECT 
							@r AS _id, 
							(SELECT @r := org_parent_id FROM vrv_org_tab WHERE id = _id) AS parent_id, 
							@l := @l + 1 AS lvl 
					FROM 
							(SELECT @r := pid, @l := 0) vars, 
							vrv_org_tab h 
					WHERE @r <> 0) T1 
			JOIN vrv_org_tab T2 
			ON T1._id = T2.id;
	SET allpid = CONCAT_WS(',',pid,sPidTemp,allpid);
END IF;
END WHILE;
RETURN allpid;
END

根据函数查询