mysql 根据id查所有父级或子级

--查询父级

SELECT T2.jgid, T2.`sjjgid` ,t2.`jgmc`
FROM (   
    SELECT   
        @r AS _id,   
        (SELECT @r := sjjgid FROM `yygl_jgxx` WHERE jgid = _id) AS 2v2,   
        @l := @l + 1 AS lvl   
    FROM   
        (SELECT @r := '09') vars,   -- --查询id为5的所有上级
        yygl_jgxx h   
    WHERE @r <> -1) T1   
JOIN yygl_jgxx T2   
ON T1._id = T2.jgid
order by jgid asc;

--查询子级 ,存储过程+临时表
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `findOrgList`(IN orgId VARCHAR(20))
BEGIN
  DROP TEMPORARY TABLE IF EXISTS tmp_org;
    -- 创建临时表
    CREATE TEMPORARY TABLE tmp_org(org_id VARCHAR(20));
    -- 清空临时表数据
    DELETE FROM tmp_org;
    -- 发起调用
    CALL findOrgChildList(orgId);
    -- 从临时表查询结果
    SELECT org_id FROM tmp_org ORDER BY org_id;
END;;
DELIMITER ;

DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `findOrgChildList`(IN orgId VARCHAR(20))
BEGIN
  DECLARE v_org VARCHAR(20) DEFAULT '';
  DECLARE done INTEGER DEFAULT 0;
    -- 查询结果放入游标中
  DECLARE C_org CURSOR FOR SELECT d.jgid
                           FROM `yygl_jgxx` d
                           WHERE d.`sjjgid` = orgId;
  DECLARE CONTINUE HANDLER FOR NOT found SET done=1;
  SET @@max_sp_recursion_depth = 10;
    
    -- 传入的组织id写入临时表
 INSERT INTO tmp_org VALUES (orgId);
  OPEN C_org;
  FETCH C_org INTO v_org;
  WHILE (done=0)
  DO
        -- 递归调用，查找下级
    CALL findOrgChildList(v_org);
    FETCH C_org INTO v_org;
  END WHILE;
  CLOSE C_org;
END;;
DELIMITER ;

mybaits调用

<select id="selectOrgChildList" resultType="java.lang.String" statementType="CALLABLE"> 
    <![CDATA[ CALL findOrgList( #{orgId,mode=IN,jdbcType=VARCHAR}, ]]> 
</select>

mysql> call `findOrgList`('00')