mysql 类似 oracel start with 树查询 自定义函数

传入 参数  thisid  tablename 

thisid 要查询 自己的 数据ID  tablename 要查询的表

要求 表 主键 ==id  标识子父关系的 字段== pid

BEGIN
		DECLARE pTemp   VARCHAR(1000);
    DECLARE cTemp VARCHAR(1000);
			DECLARE te   VARCHAR(1000);
		
    SET pTemp = '$';
    SET cTemp = cast(thisid as CHAR);
  
    WHILE      cTemp is not null DO SET pTemp = concat(pTemp, ',', cTemp);
    set        @_sql = concat(' select CONCAT(a.ids) into @cTemp_result from ( SELECT group_concat(id) ids FROM ',
                            tablename,
                             ' WHERE FIND_IN_SET(pid,''',cTemp,''')>0 ) a ');
    PREPARE    a FROM @_sql;
    EXECUTE    a;
		DEALLOCATE PREPARE a;
	set cTemp = @cTemp_result;
  END WHILE;
  set @_sql = concat('SELECT * FROM ',
                    tablename,
                    ' WHERE FIND_IN_SET(id,''', pTemp,''') and id!=',
                   thisid);
  PREPARE b FROM @_sql;
  EXECUTE b;
  DEALLOCATE PREPARE b;
END

所有的测试数据  ID -PID 为 子父关系

执行查询 结果