sql递归

1. 方法一：递归思想

select id from (
              select t1.id,
              if(find_in_set(p_id, @pids) > 0, @pids := concat(@pids, ',', id), 0) as ischild
              from (
                   select id,p_id from org t  order by p_id, id
                  ) t1,
                  (select @pids := 1) t2
             ) t3 where ischild != 0

2.方法二：储存过程

CREATE FUNCTION `getChildListS`(rootId INT)
RETURNS varchar(1000) 
 
BEGIN
	  #声明两个局部变量
    DECLARE sTemp VARCHAR(1000);
    DECLARE sTempChd VARCHAR(1000);
		#初始化局部变量
    SET sTemp = '$';
		#调用cast函数将int转换为char
    SET sTempChd =cast(rootId as CHAR);
    #递归拼接
    WHILE sTempChd is not null DO
				#存储每次递归结果
        SET sTemp = concat(sTemp,',',sTempChd);
				#将参数作为pid，然后查询其子id，然后将子id作为pid，
        #查询以子id为pid的子id，依次循环下去，直到所有节点都为叶子节点
        SELECT group_concat(id) INTO sTempChd FROM  org where FIND_IN_SET(id,sTempChd)>0;
    END WHILE;
    RETURN sTemp; 
END
 
 
 
#测试
select * from org where getChildListS(2)