mysql 找出最大父级_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调用

mysql> call `findOrgList`('00')