目录结构问题

Question

想将目录结构保存入数据库，考虑借鉴类别的方式存储目录结构：
id      name          father          layer但有个问题，如何获得一个目录的路径呢？感觉查询起来很不方便，还有啥更好的存储方法没有？

Answer 1

--自己定义一个函数：
/*------------------------
--try
if object_id(N'tt','U') is not null
drop table tt
go
create table tt(id int primary key, name nvarchar(100), father int, layer int)go
insert tt values(1,'a',null,0);
insert tt values(2,'aa',1,1);
insert tt values(3,'ab',1,1);
insert tt values(4,'aaa',2,2);
insert tt values(5,'aba',3,2);if object_id(N'fun','fn') is not null
drop function fun
go
create function fun(@id int)
returns nvarchar(1000)
as
begin
declare @layer int,@name nvarchar(100),@father int
declare @result nvarchar(1000)
select @layer = layer, @name=name, @father=father from dbo.tt where id=@id;
if @layer = 0
set @result = '/' + @name;
else
set @result = dbo.fun(@father) + '/' + @name;
return @result 
endgo
select * from tt
select id,path=dbo.fun(id) from tt
------------------------*/(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)
         id name                                                                                                      father       layer
----------- ---------------------------------------------------------------------------------------------------- ----------- -----------
          1 a                                                                                                           NULL           0
          2 aa                                                                                                             1           1
          3 ab                                                                                                             1           1
          4 aaa                                                                                                            2           2
          5 aba                                                                                                            3           2(5 row(s) affected)         id path
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
          1 /a
          2 /a/aa
          3 /a/ab
          4 /a/aa/aaa
          5 /a/ab/aba(5 row(s) affected)

Answer 2

非常感谢，解决了一个大问题！
但在遍历目录时又遇到一个问题，头疼http://community.csdn.net/Expert/topic/5724/5724160.xml?temp=.7520716