SQL树结构查询（根据id查找当前路径）

有如下图所示的表r_directory，要根据id，查找到当前的路径

我们的期望是当查询的ID_DIRECTORT=15时返回的路径为：Basic_DW/Basic_DW/DW_JB/岳

为此我写了一个函数来帮助我完成这一功能

CREATE DEFINER=`root`@`localhost` FUNCTION `find_path`(`id` int) RETURNS varchar(100) CHARSET utf8
BEGIN
	#Routine body goes here...

	DECLARE sTemp INTEGER DEFAULT id;
	DECLARE re_str VARCHAR(100) DEFAULT '';
	DECLARE smap VARCHAR(20);
	DECLARE uid INTEGER;
	select  DIRECTORY_NAME  into re_str FROM r_directory where ID_DIRECTORY = sTemp;
	while exists(select 1 from r_directory where ID_DIRECTORY = id and ID_DIRECTORY_PARENT is not null )
  	DO
		  SET smap = '';
			SET uid  = null;
    	select b.ID_DIRECTORY INTO  uid  from  r_directory  a , r_directory  b where a.ID_DIRECTORY = id and a.ID_DIRECTORY_PARENT = b.ID_DIRECTORY;
  		select b.DIRECTORY_NAME INTO smap from  r_directory  a , r_directory  b where a.ID_DIRECTORY = id and a.ID_DIRECTORY_PARENT = b.ID_DIRECTORY;
			SET id = uid;
			IF smap != '' 
			THEN SET re_str = CONCAT(smap,'/',re_str);
      END IF;
  	END while;
	return re_str;
END

完成后 运行函数输入15  即可得到想要的结果

以上，完成了sql的查询。