mysql 父类子类查询

查出子
DELIMITER $$


DROP FUNCTION IF EXISTS `getDeptChildList`$$
CREATE FUNCTION `getDeptChildList`(rootId INT) RETURNS varchar(1000) CHARSET utf8
    READS SQL DATA
    DETERMINISTIC
BEGIN 
       DECLARE pTemp VARCHAR(1000);  
       DECLARE cTemp VARCHAR(1000); 
      
       SET pTemp = '$';  
       SET cTemp =cast(rootId as CHAR);
      
       WHILE cTemp is not null DO  
         SET pTemp = concat(pTemp,',',cTemp);
         SELECT group_concat(id) INTO cTemp FROM roll_work_comment   
         WHERE FIND_IN_SET(mes_id,cTemp)>0; 
       END WHILE;  
       RETURN pTemp;  
     END;


 $$


DELIMITER ;
SELECT * FROM roll_work_comment  WHERE FIND_IN_SET(id,getDeptChildList(1)) ORDER BY id and mes_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