Mysql递归查询子级（父子级结构）&从子级ID查询所有父级（及扩展知识）

ps酷教程

已于 2024-05-26 10:05:27 修改

阅读量2w

点赞数 38

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

于 2021-12-08 09:31:08 首次发布

本文链接：https://blog.csdn.net/qq_16992475/article/details/121783805

版权

Dao 专栏收录该内容

19 篇文章 0 订阅

订阅专栏

文章目录

1、建表及插入数据
2、递归查询子级(包括or不包括自己)
3、从子类ID查询所有父类
- sql
4、使用示例

1、建表及插入数据


DROP TABLE IF EXISTS `merchant_region`;
CREATE TABLE `merchant_region`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) NULL DEFAULT NULL,
  `name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 13 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `merchant_region` VALUES (1, 0, '1');
INSERT INTO `merchant_region` VALUES (2, 0, '2');
INSERT INTO `merchant_region` VALUES (3, 0, '3');
INSERT INTO `merchant_region` VALUES (4, 1, '1-1');
INSERT INTO `merchant_region` VALUES (5, 4, '1-1-1');
INSERT INTO `merchant_region` VALUES (6, 4, '1-1-2');
INSERT INTO `merchant_region` VALUES (7, 6, '1-1-2-1');
INSERT INTO `merchant_region` VALUES (8, 2, '2-1');
INSERT INTO `merchant_region` VALUES (9, 2, '2-2');
INSERT INTO `merchant_region` VALUES (10, 3, '3-1');
INSERT INTO `merchant_region` VALUES (11, 1, '1-2');
INSERT INTO `merchant_region` VALUES (12, 11, '1-2-1');

在这里插入图片描述

2、递归查询子级(包括or不包括自己)

递归查询子级sql

示例sql


select  * from merchant_region;

-- 发现使用字符型和使用数字类型查询结果在显示上不一致,所以需要转
select @ids;
select @ids:= 0;
select @ids:= '0';
select @ids:= cast(0 as char);

-- 核心(作最坏的打算->假设表中数据所代表的每一个节点都是上一个节点的子节点)
select @ids as _ids,
(
	select @ids:=group_concat(mr.id)
	from merchant_region mr
	where find_in_set(mr.parent_id,@ids)
)
from merchant_region

-- 为了更好的理解上面这个逻辑，可以执行如下的sql帮助理解
select @ids := null;
	
select @ids := if(@ids is null,1,@ids:=@ids+1) ,mr.* from merchant_region mr


-- 最终写法
select 
	mr.* 
from 
	merchant_region mr 
	inner join (select @ids:= cast(0 as char)) t1 -- 将@ids初始化,并且将数字转为字符类型
	inner join (
		select @ids as _ids1,
		(
			select @ids:=group_concat(mr.id)
			from merchant_region mr
			where find_in_set(mr.parent_id,@ids)
		) as _ids2
		from merchant_region
		where @ids is not null -- 排除掉后面都是null的数据行
	) t2
where 
	find_in_set(mr.id,t2._ids1) -- 这里选择_ids1或_ids2来决定是否包含自身

-- 使用示例
<select id="selectHierarchyRegion" resultType="com.anbao.ambientMonitor.data.dto.region.MerchantRegionCustomPropertyDTO">
    SELECT
        mr.id,
        mr.region_name AS label,
        mr.parent_id,
        mr.region_remark,
        mur.merchant_sub_user_id
    FROM
        merchant_user_region mur
        LEFT JOIN merchant_region mr ON mr.id = mur.merchant_region_id
       ,(SELECT @ids := 0) b
       ,(SELECT @ids AS _ids,(SELECT	@ids := GROUP_CONCAT(id) FROM merchant_region	WHERE	FIND_IN_SET(parent_id, @ids)) AS cids FROM merchant_region WHERE @ids IS NOT NULL ) a
    WHERE
        mur.merchant_sub_user_id = #{merchantUserId}
      AND FIND_IN_SET(mur.merchant_region_id,a._ids)
      AND mr.merchant_id = #{merchantId}
      AND mr.region_classify = 1
      AND mr.is_del = 0
    ORDER BY CONVERT(mr.region_name USING gbk)
</select>

可能存在的问题

有时候，上面这条sql查询，每次查询的结果有不一致的情况，原因不明。但须执行下面这条代码，就可以解决，不知道为什么

baseMapper.removeCache(1L)；

<update id="removeCache">
      SELECT
          	@ids AS _ids,
			(
				SELECT
					@ids := GROUP_CONCAT(id)
				FROM
					merchant_region
				WHERE
					FIND_IN_SET(parent_id, @ids)
			) AS cids
      FROM
          merchant_region,
          (SELECT @ids := #{regionId}) b
      WHERE
          @ids IS NOT NULL
  </update>

处理这个存在的问题

上面查询，有的时候查出的来的结果不一致，可以先把后面的查询先查出来作为临时表，再使用Left JOIN连接起来，下面的CAST是因为单独查询这个临时表，也有点问题。

SELECT
		d.id,
		d.name,
		d.parent_id
FROM
		dept d
		LEFT JOIN(
			SELECT * FROM (
				(SELECT @ids := CAST(#{parentId} AS char)) a
				, (SELECT @ids AS _ids ,
						  (SELECT @ids := GROUP_CONCAT(id) FROM dept WHERE FIND_IN_SET(parent_id, @ids)) AS cids
				   FROM
					  	dept
				   WHERE
						@ids IS NOT NULL
				) b
			)
		) T ON FIND_IN_SET(d.id, T._ids)
WHERE
		d.hospital_area_id = #{hospitalAreaId}
		AND d.is_del = 0
ORDER BY
		d.sort_index DESC

可借鉴的扩展参考(重要)

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

根据一个父节点查询所有子节点（包含自身）

SELECT au.id 
FROM (SELECT * FROM t_app_user WHERE parent_id IS NOT NULL) au,
     (SELECT @pid := '1') pd 
WHERE FIND_IN_SET(parent_id, @pid) > 0 
  AND @pid := concat(@pid, ',', id)
union select id from t_app_user where id = '1';

根据多个父节点查询所有子节点（包含自身）

SELECT au.id 
FROM (SELECT * FROM t_app_user WHERE parent_id IS NOT NULL) au,
     (SELECT @pid := '1,4') pd 
WHERE FIND_IN_SET(parent_id, @pid) > 0 
  AND @pid := concat(@pid, ',', id) 
union select id from t_app_user where FIND_IN_SET(id,@pid) > 0;

MYSQL根据id递归向下查询所有子级

SELECT
	ID.LEVEL,
	DATA.* 
FROM
	(
	SELECT
		@ids AS _ids,
		( SELECT @ids := GROUP_CONCAT( id ) FROM 表名 WHERE FIND_IN_SET( 父级 id字段, @ids ) ) AS cids,
		@l := @l + 1 AS LEVEL 
	FROM
		表名,
		( SELECT @ids := '条件id', @l := 0 ) b 
	WHERE
		@ids IS NOT NULL 
	) ID,
	表名 DATA 
WHERE
	FIND_IN_SET( DATA.id, ID._ids ) 
ORDER BY
	LEVEL,
	id

MySQL 根据部门ID 查询当前部门&部门下的所有子级部门

SELECT
    id,pid,name
FROM
    (
    SELECT
        t1.id,t1.name,t1.pid,
        IF ( find_in_set( pid, @pids ) > 0, @pids := concat( @pids, ',', id ), -1 ) AS ischild
    FROM
        ( SELECT id, pid,name FROM '部门表名' t ) t1,
        ( SELECT @pids := #{id} ) t2
    ) t3
WHERE
    ischild != -1
or id = #{id}	-- 去掉这句则不显示当前部门

MySQL 根据部门ID 查询当前部门&部门的所有上级部门

SELECT T2.id, T2.name, T1.lvl as level
        FROM (
        SELECT
        @r AS _id,
        (SELECT @r := pid FROM '部门表名' WHERE id = _id) AS pid,
        @l := @l + 1 AS lvl
        FROM
        (SELECT @r := #{部门id}, @l := 0) vars,
        '部门表名' h
        WHERE @r != 0) T1
        JOIN '部门表名' T2
        ON T1._id = T2.id
        ORDER BY T1.lvl DESC

场景1

场景：假设有一个部门表，它是存在上下级的，就形成了一颗部门树。每一个部门节点都可以被选中，也就是可多选节点（如果选中了某个节点，那么这个节点下的所有节点取消勾选，因为上级都包括了，下级就不用勾选了。但是部门树的另外分支同时是可选的）。查询的时候，要求查询所选择节点，以及所有所选择节点及这些节点下的所有节点

    <select id="selectAllSubDeptByDeptIds" resultType="long">
        SELECT
            d.id
        FROM
            dept d
            INNER JOIN(
                SELECT * FROM (
                    (SELECT @ids :=
                            '${@org.springframework.util.StringUtils@collectionToCommaDelimitedString(deptIdList)}'
                    ) a
                    , (SELECT @ids AS _ids ,
                              (SELECT @ids := GROUP_CONCAT(id) FROM dept WHERE FIND_IN_SET(parent_id, @ids)) AS cids
                       FROM
                            dept d
                       WHERE
                            @ids IS NOT NULL AND d.is_del = 0
                    ) b
                )
            ) T ON FIND_IN_SET(d.id, T.cids)
        WHERE
            d.is_del = 0
    </select>

场景2

场景：上面场景的一个应用。按部门取消课程邀请，可以一次取消多个部门，取消部门的时候，所有所选择的部门及所选择部门下的子级部门都要删除掉。

<delete id="deleteCourseInvitationSubDepts">
   DELETE
   FROM
       course_invitation ci
   WHERE
       ci.course_id = #{courseId}
       AND FIND_IN_SET (ci.dept_id,
       (
           SELECT
              group_concat( cids )
           FROM (
                  ( SELECT @ids :=
                      '${@org.springframework.util.StringUtils@collectionToCommaDelimitedString(deptIdList)}'
                  ) a,
                  (SELECT
                      @ids AS _ids,
                      (SELECT
                          @ids := GROUP_CONCAT( id )
                      FROM
                          dept
                      WHERE
                          FIND_IN_SET( parent_id, @ids )
                      ) AS cids
                  FROM
                      dept
                  WHERE
                      @ids IS NOT NULL
                  ) b
                )
           )
       )
</delete>

扩展-序号

依据上面的原理，我们也可以给查出来的数据添加一个简单的序号，类似于下面这种

在这里插入图片描述

select 
	mr.*,
	t1.*,
	(@ids:=@ids+1) _ids -- 序号列
from 
	merchant_region mr 
	inner join (select @ids:= 0) t1

原理

原理篇

在这里插入图片描述

获取一组连续的天数

来源：mysql按照天统计报表，当天没有数据，填0

SELECT
    @cdate := DATE_ADD(@cdate, INTERVAL - 1 DAY) DAY
  FROM
    (SELECT
      @cdate := DATE_ADD('20171219', INTERVAL + 1 DAY)
    FROM
      order) t0
  LIMIT 7

其它可参考

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

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

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

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

Oracle 递归查询

https://blog.csdn.net/qq_26542493/article/details/107754967

mysq存储函数实现

MySql中使用函数实现递归查询子节点和父节点

存储路径的做法

在建表的时候，定义3个字段：id、parent_id、route_id，其中route_id要记录所有的父级id，比如：1/3/6/9/，注意最后面的这个/不能省略哦，然后mysql查询的时候，使用右边like % 就可以查询出所有的子级（如果需要不包括自己，那么再加上不等与自己的id即可），同时，也可以，查询出所有的父级（因为每个节点都记录了所有的父级id，可以再代码里面查，也可以在sql里面写子查询去查），这样做也比较靠谱

逗号分隔的做法

在建表的时候，定义3个字段：id、parent_id、route_id，其中route_id要记录所有的父级id，比如：1,3,6,9，并且使用","分隔开来，当需要查询某个父级下的所有子级时，可以使用find_in_set(指定父级的id,route_id)来查询，如果某条数据的id属于指定父级，那么这个id对应数据的route_id中必然要会有父级id

ancestor的做法

一张表dept表记录所有的部门数据，再加一张dept_ancestor表记录所有的父级关系（包括隔代父级关系）。这样每条数据的所有父级都可以知道，每条数据的所有子级也都可以知道。就是维护起来操作比较麻烦。

自定义函数

getParentNodeList

#函数
CREATE DEFINER=`root`@`%` FUNCTION `yd_cloud_uaa`.`getParentNodeList`(`nodeId` bigint) RETURNS longtext CHARSET utf8
    DETERMINISTIC
BEGIN
    DECLARE parentList LONGTEXT;      # 返回父节点结果集
    DECLARE tempParent LONGTEXT;      # 临时存放父节点
    DECLARE total int;      # 临时存放父节点

    SET parentList = '';
    SET tempParent = CAST(nodeId as CHAR); # 将int类型转换为String

    WHILE tempParent is not null DO        # 循环，用于查询节点上所有的父节点
        SET parentList = CONCAT(parentList, ',', tempParent);   # 存入到返回结果中
    SELECT  count(*) into total FROM `yd_cloud_uaa`.`uaa_permission` where id = tempParent;
    if total = 0 
    then 
      set tempParent :=null;
    else
          SELECT parent_id INTO tempParent FROM `yd_cloud_uaa`.`uaa_permission` where id = tempParent limit 1;   # 查询节点上所有父节点
     end if;
    
    END WHILE;
    RETURN SUBSTRING(parentList, 2);       # 将返回结果处理，截取掉结果集前面的逗号
END

getChildLst

CREATE DEFINER=`root`@`%` FUNCTION `getChildLst`(rootId LONG) RETURNS varchar(1000) CHARSET utf8mb4
BEGIN
       DECLARE sTemp VARCHAR(1000);
       DECLARE sTempChd VARCHAR(1000);
    
       SET sTemp = '$';
       SET sTempChd =cast(rootId as CHAR);
    
       WHILE sTempChd is not null DO
         SET sTemp = concat(sTemp,',',sTempChd);
         SELECT group_concat(id) INTO sTempChd FROM uaa_permission where FIND_IN_SET(parent_id,sTempChd)>0;
       END WHILE;
       RETURN sTemp;
     END

自定义函数2

建表造数据测试

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for t_tree
-- ----------------------------
DROP TABLE IF EXISTS `t_tree`;
CREATE TABLE `t_tree` (
                          `id` int(20) NOT NULL AUTO_INCREMENT,
                          `parent_id` int(20) DEFAULT NULL,
                          PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

-- ----------------------------
-- Records of t_tree
-- ----------------------------
BEGIN;
INSERT INTO `t_tree` VALUES (1, 0);
INSERT INTO `t_tree` VALUES (2, 0);
INSERT INTO `t_tree` VALUES (3, 1);
INSERT INTO `t_tree` VALUES (4, 2);
INSERT INTO `t_tree` VALUES (5, 2);
INSERT INTO `t_tree` VALUES (6, 3);
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

创建函数

# 注：如果创建函数时报错： 1418 - This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable), Time: 0.006000s

# 原因：
# 开启了bin-log, 必须指定我们的函数是否是
# 1 DETERMINISTIC 不确定的
# 2 NO SQL 没有SQl语句，当然也不会修改数据
# 3 READS SQL DATA 只是读取数据，当然也不会修改数据
# 4 MODIFIES SQL DATA 要修改数据
# 5 CONTAINS SQL 包含了SQL语句

# 解决：
# 《法一》信任子程序的创建者，禁止创建、修改子程序时对SUPER权限的要求，设置log_bin_trust_routine_creators全局系统变量为1。设置方法有三种:
# 1.在客户端上执行
SET GLOBAL log_bin_trust_function_creators = 1;
# 2.MySQL启动时，加上 –log-bin-trust-function-creators，参数设置为1
# 3.在MySQL配置文件my.ini或my.cnf中的[mysqld]段上加log-bin-trust-function-creators=1

# 《法二》在创建子程序(存储过程、函数、触发器)时，声明为DETERMINISTIC或NO SQL与READS SQL DATA中的一个，即下面创建函数

函数 getChildId => 查询根结点的所有子结点

DROP FUNCTION IF EXISTS `getChildId`;
DELIMITER ;;
CREATE FUNCTION `getChildId`(areaId varchar(20)) RETURNS varchar(4000) CHARSET utf8

    -- 声明为DETERMINISTIC
    DETERMINISTIC
    
BEGIN
            DECLARE sTemp VARCHAR(4000);
            DECLARE sTempChd VARCHAR(4000);
            SET sTemp='';
            SET sTempChd = CAST(areaId AS CHAR);
            WHILE sTempChd IS NOT NULL DO
						IF sTemp!='' THEN
            SET sTemp= CONCAT(sTemp,',',sTempChd);
						ELSE
					  SET sTemp=sTempChd;
						END IF;
            SELECT GROUP_CONCAT(id) INTO sTempChd FROM t_tree WHERE FIND_IN_SET(CAST(parent_id AS SIGNED),sTempChd)>0;
            END WHILE;
            RETURN sTemp;
            END
;;
DELIMITER ;

函数 getParentId => 查询子结点的所有父结点

DROP FUNCTION IF EXISTS `getParentId`;
DELIMITER ;;
CREATE FUNCTION `getParentId`(areaId varchar(20)) RETURNS varchar(4000) CHARSET utf8

     -- 声明为DETERMINISTIC
    DETERMINISTIC

BEGIN
            DECLARE sTemp VARCHAR(4000);
            DECLARE sTempChd VARCHAR(4000);
            SET sTemp='';
 
						SELECT group_concat(T2.id) INTO sTemp
            FROM (
                  SELECT
                    @r AS _id,
                    (SELECT @r := parent_id FROM t_tree WHERE id = _id) AS parent_id,
                    @l := @l + 1 AS lvl FROM (SELECT @r := areaId, @l := 0) vars,
                    t_tree h WHERE @r <> 0 AND parent_id > 0
                 ) T1 JOIN t_tree T2 ON T1._id = T2.id ORDER BY T1.lvl DESC;
						
            RETURN sTemp;
            END
;;
DELIMITER ;

使用

select * from t_tree where FIND_IN_SET(id,getChildId('1'));
select * from t_tree where FIND_IN_SET(id,getParentId('2'));

3、从子类ID查询所有父类

mysql中从子类ID查询所有父类（做无限分类经常用到）

参考：https://www.cnblogs.com/biehongli/p/9391757.html

在这里插入图片描述

sql

SELECT
	T2.id,T2.parent_id,T2.name,T1.lvl 
FROM
	(SELECT @r := 7, @l := 0) vars, --  “7” 可以使用 #{regionId}
	(SELECT
		@r AS _id,
		(SELECT 
			@r := parent_id
		 FROM 
			merchant_region 
		 WHERE 
			id = _id    -- 注意: 查询条件这里不要写@用户变量(试了好多,发现查询结果没什么固定的规律,这也可能是前面要写@r as _id的原因) 
		) AS parent_id,
		@l := @l + 1 AS lvl 
	 FROM
		merchant_region h 
	 WHERE
		@r != 0 
	) T1
	JOIN merchant_region T2 ON T1._id = T2.id 
ORDER BY
	T1.lvl DESC

4、使用示例

<!-- 需求：区域和组织是多对多的关系，保存在organization_region中间表中，一个组织可以对应多个区域。 -->
<!--用户可以对应多个区域(多对多)，保存在用户和区域的中间表merchant_user_region，区域之间存在层级关系 -->
<select id="selectRegionIdByOrganizationId" resultType="java.lang.Long">
	 SELECT
	     orr.region_id
	 FROM
	     organization_region orr
	         INNER JOIN (
	         SELECT
	             mr.id
	         FROM
	             (SELECT @ids := 0) b,
	             (
	                 SELECT
	                     @ids AS _ids,
	                     (SELECT @ids := GROUP_CONCAT(id) 
	                      FROM merchant_region 
	                      WHERE FIND_IN_SET(parent_id, @ids)
	                     ) AS cids
	                 FROM 
	                 	merchant_region
	                 WHERE
	                     @ids IS NOT NULL) c1,
	             merchant_region mr
	             LEFT JOIN merchant_user_region mur ON mr.id = mur.merchant_region_id
	         where
	             FIND_IN_SET(mr.id, c1._ids)
	           AND mr.is_del = 0
	           AND mur.merchant_sub_user_id = #{merchantSubUserId}
	         ORDER BY mr.create_time DESC) mr ON mr.id = orr.region_id
	 WHERE
	     orr.organization_id = #{id}
</select>

补充：根据中间关系表筛选具有权限的数据

<select id="queryList" resultType="com.anbao.ambientMonitor.data.dto.device.DeviceInfoDTO">
        SELECT
            a.*
        FROM
            (
                SELECT
                    d.id AS deviceId,
                    d.device_code,
                    d.device_model,
                    d.device_name,
                    d.online_status,
                    d.hardware_ver,
                    d.firmware_ver,
                    d.region_id,
                    d.last_communication_time,
                    d.bind_time
                FROM
                    device d
                        LEFT JOIN merchant_region mr ON d.region_id = mr.id
                        LEFT JOIN merchant_user_region mur ON mr.id = mur.merchant_region_id
                        INNER JOIN (SELECT @ids := GROUP_CONCAT(mr.id) aaaa FROM merchant_region mr WHERE mr.merchant_id = #{params.merchantId}) a
                WHERE
                    d.merchant_id = #{params.merchantId}
                  AND FIND_IN_SET(mur.merchant_region_id ,@ids)
                  AND d.is_del = 0
                  AND mur.merchant_sub_user_id = #{params.merchantSubUserId}
                  AND d.device_model = #{params.deviceModel}
                UNION ALL
                SELECT
                    d.id AS deviceId,
                    d.device_code,
                    d.device_model,
                    d.device_name,
                    d.online_status,
                    d.hardware_ver,
                    d.firmware_ver,
                    d.region_id,
                    d.last_communication_time,
                    d.bind_time
                FROM
                    device d
                WHERE
                    d.merchant_id = #{params.merchantId}
                  AND d.is_del = 0
                  AND d.device_model = #{params.deviceModel}
                  AND d.region_id IS NULL
            ) a
        ORDER BY
            a.bind_time DESC
    </select>