有如下图所示的表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的查询。