mysql实现无限层次父子关系查询，并返回当前查询level层级

最新推荐文章于 2024-06-28 12:32:50 发布

sinJack

最新推荐文章于 2024-06-28 12:32:50 发布

阅读量1.3w

点赞数 5

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/qq_40693603/article/details/107693242

版权

mysql 专栏收录该内容

5 篇文章 1 订阅

订阅专栏

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

create table CS_INTCTL_ASSESS_ITEM_GATHER
(
 id int(11) PRIMARY key auto_increment,
 assess_id   VARCHAR(36),
 assess_parent_id  VARCHAR(36),
 name        VARCHAR(256)
)

insert into cs_intctl_assess_item_gather (ASSESS_ID, ASSESS_PARENT_ID, NAME)
values ( 'c46a67ffa5dc4cd990e9402dd5f21e56', '7540f5592a794ec0982bbea817d4b8ce', '一、组织结构评价（20分）');
insert into cs_intctl_assess_item_gather (ASSESS_ID, ASSESS_PARENT_ID, NAME)
values ('7d77b36468e04e96a56af4f105225fac', '7540f5592a794ec0982bbea817d4b8ce', '二、经营理念与风格评价（14分）');
insert into cs_intctl_assess_item_gather (ASSESS_ID, ASSESS_PARENT_ID, NAME)
values ('7540f5592a794ec0982bbea817d4b8ce', 'd24e44cbc0c34452b1da93739adfe716', '内部环境');
insert into cs_intctl_assess_item_gather (ASSESS_ID, ASSESS_PARENT_ID, NAME)
values ('15f1abe4b9544f7d900f6ccec9842256', '13af09d9e8ad4da297e72e3020e290c8', '一、信贷基础管理评价（10分）');
insert into cs_intctl_assess_item_gather (ASSESS_ID, ASSESS_PARENT_ID, NAME)
values ('73a60d0ed27c40718b3fd707400416af', '13af09d9e8ad4da297e72e3020e290c8', '二、评级授信评价（15分）');
insert into cs_intctl_assess_item_gather (ASSESS_ID, ASSESS_PARENT_ID, NAME)
values ('13af09d9e8ad4da297e72e3020e290c8', '08c080e87e134183b239cc7409be5210', '信贷业务');
insert into cs_intctl_assess_item_gather (ASSESS_ID, ASSESS_PARENT_ID, NAME)
values ('08c080e87e134183b239cc7409be5210', 'd24e44cbc0c34452b1da93739adfe716', '控制活动（55％）');
insert into cs_intctl_assess_item_gather (ASSESS_ID, ASSESS_PARENT_ID, NAME)
values ('d24e44cbc0c34452b1da93739adfe716', '0', '本部评价（70％）');
insert into cs_intctl_assess_item_gather (ASSESS_ID, ASSESS_PARENT_ID, NAME)
values ('c46a67ffa5dc4cd990e9402dd5f21e56', '7540f5592a794ec0982bbea817d4b8ce', '一、组织结构评价（20分）');
insert into cs_intctl_assess_item_gather (ASSESS_ID, ASSESS_PARENT_ID, NAME)
values ('7d77b36468e04e96a56af4f105225fac', '7540f5592a794ec0982bbea817d4b8ce', '二、经营理念与风格评价（14分）');
insert into cs_intctl_assess_item_gather (ASSESS_ID, ASSESS_PARENT_ID, NAME)
values ('7540f5592a794ec0982bbea817d4b8ce', 'd24e44cbc0c34452b1da93739adfe716', '内部环境');
insert into cs_intctl_assess_item_gather (ASSESS_ID, ASSESS_PARENT_ID, NAME)
values ('15f1abe4b9544f7d900f6ccec9842256', '13af09d9e8ad4da297e72e3020e290c8', '一、信贷基础管理评价（10分）');
insert into cs_intctl_assess_item_gather (ASSESS_ID, ASSESS_PARENT_ID, NAME)
values ('73a60d0ed27c40718b3fd707400416af', '13af09d9e8ad4da297e72e3020e290c8', '二、评级授信评价（15分）');
insert into cs_intctl_assess_item_gather (ASSESS_ID, ASSESS_PARENT_ID, NAME)
values ('13af09d9e8ad4da297e72e3020e290c8', '08c080e87e134183b239cc7409be5210', '信贷业务');

二、查询

1、通过普通的sql查询
查询 ASSESS_ID=0的所有子级，并返回查询的子级层级

SELECT DISTINCT c1.level-1 level, c2.assess_id,c2.assess_parent_id FROM( 
	SELECT 
	@ids as _ids, 
	( SELECT @ids := GROUP_CONCAT(assess_id) 
	FROM cs_intctl_assess_item_gather 
	WHERE FIND_IN_SET(assess_parent_id, @ids) 
	) as cids, 
	@l := @l+1 as level 
	FROM cs_intctl_assess_item_gather, 
	(SELECT @ids :='0', @l := 0 ) b 
	WHERE @ids IS NOT NULL 
) c1, cs_intctl_assess_item_gather c2 
WHERE FIND_IN_SET(c2.assess_id, c1._ids)
ORDER BY level, assess_id

在这里插入图片描述
2、通过定义mysql函数实现

-- 定义mysql函数
DROP FUNCTION IF EXISTS queryChildrenInfo;DELIMITER ;;
CREATE FUNCTION queryChildrenInfo(areaId INT)
RETURNS VARCHAR(4000)
BEGIN
DECLARE sTemp VARCHAR(4000);
DECLARE sTempChd VARCHAR(4000);
SET sTemp='$';
SET sTempChd = CAST(areaId AS CHAR);

WHILE sTempChd IS NOT NULL DO
SET sTemp= CONCAT(sTemp,',',sTempChd);
SELECT GROUP_CONCAT(assess_id) INTO sTempChd FROM CS_INTCTL_ASSESS_ITEM_GATHER WHERE FIND_IN_SET(assess_parent_id,sTempChd)>0;
END WHILE;
RETURN sTemp;
END;;
DELIMITER ;

-- 调用函数
SELECT DISTINCT assess_id,assess_parent_id FROM CS_INTCTL_ASSESS_ITEM_GATHER WHERE FIND_IN_SET(assess_id,queryChildrenInfo('0'))