树状结构表中,获取指定节点的所有父节点路径

作者: 爱情像杯酒
来源: 51数据库
2022-08-17

drop table if exists `group`;

create table `group` (
	`id` int(11) not null auto_increment,
	`parent_group_id` int(11) not null default '-1',
	`name` varchar(255) not null,
	primary key (`id`)
);

insert into `group` (`id`, `name`, `parent_group_id`) values (1, 'a', -1);
insert into `group` (`id`, `name`, `parent_group_id`) values (2, 'b', -1);
insert into `group` (`id`, `name`, `parent_group_id`) values (3, 'c',  1);


/**
 * 返回树状结构表中指定节点的父节点路径.
 * 张露兵 zhanglubing927@163.com
 * 2012-2-21
 */
drop procedure if exists get_path;

delimiter $
create procedure get_path(in id int) 
begin

	declare gid int default id; 
	declare path varchar(255) default '';
	
	while gid is not null and gid != -1 do
		select concat(concat(g.name,'(', g.id, ')'), '-', path), g.parent_group_id into path, gid 
		from `group` g where g.id = gid;
	end while;
	
	select substring(path, 1, length(path)-1) 'path'; 
end
$

-- call get_path(3);
-- a(1)-c(3)

推荐阅读