关于MySQL递归查询，从子类ID查询所有父类，从父类ID查询所有子类

一、业务场景

业务需要根据一个子节点查询出根节点的信息。做无限分类经常用到。

1.子部门id查所有父级部门

代码如下（示例）：

SELECT T2.*
   FROM (
   SELECT
   @r AS _id,
   (SELECT @r := parent_id FROM tbl_app_dept WHERE id = _id) AS parent_id, @l := @l + 1 AS lvl
   FROM
   (SELECT @r := '232', @l := 0) vars, tbl_app_dept h WHERE @r <> 0) T1
   JOIN tbl_app_dept T2 ON T1._id = T2.id
   ORDER BY T1.lvl DESC

2.oracle connect by实现

#由叶子节点查根节点
select * from table start with id=1 connect by id = prior p_id
#由父节点查叶子节点
select * from table start with p_id=4 connect by prior id = p_id

3.存储过程查看所有子部门id

CREATE DEFINER=`root`@`%` FUNCTION `test_db`.`getChildList`(rootId varchar(1000)) RETURNS mediumtext CHARSET utf8
    DETERMINISTIC
    COMMENT '查询所有子部门id'
BEGIN
    DECLARE sTemp MEDIUMTEXT;
       DECLARE sTempChd MEDIUMTEXT;

       SET sTemp = '$';
       SET sTempChd =cast(rootId as CHAR);

       WHILE sTempChd is not null DO
         SET sTemp = concat(sTemp,',',sTempChd);
         SELECT group_concat(dept_id) INTO sTempChd FROM tbl_app_dept where FIND_IN_SET(parent_id,sTempChd)>0;
       END WHILE;
       RETURN sTemp;
END