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)
用户登录
还没有账号?立即注册
用户注册
投稿取消
| 文章分类: |
|
还能输入300字
上传中....
