mysql 父类查询所有子类以及从子类查询父类

父类查询所有子类函数

CREATE FUNCTION getChildeIds (rootId INT) RETURNS VARCHAR (1000)
BEGIN
  DECLARE sTemp VARCHAR (1000) ;
  DECLARE sTempChd VARCHAR (1000) ;
  SET sTemp = '0' ;
  SET sTempChd = CAST(rootId AS CHAR) ;
  WHILE
SET sTemp = CONCAT(sTemp,',',sTempChd);
    SELECT
      GROUP_CONCAT(子ID) INTO sTempChd
    FROM
      platform_depart
    WHERE FIND_IN_SET(父id, sTempChd) > 0 ;
  END WHILE ;
  SET sTemp=substring(sTemp,3);
   RETURN sTemp;
END$$

FIND_IN_SET( ID, getChildeIds (ID) )

!FIND_IN_SET( ID, getChildeIds (ID) )

查出父
SELECT T2.*
FROM ( 
    SELECT 
        @r AS _id, 
        (SELECT @r := mes_id FROM roll_work_comment WHERE id = _id) AS mes_id, 
        @l := @l + 1 AS lvl 
    FROM 
        (SELECT @r := 3, @l := 0) vars, 
        roll_work_comment h 
    WHERE @r <> 0) T1 
JOIN roll_work_comment T2 
ON T1._id = T2.id 
ORDER BY T1.lvl DESC