使用T-SQL生成树形目录(引用)

  使用T-SQL生成树形目录(引用) 收藏
--创建测试数据表.
create table tb(id int,name varchar(10),parentid int)
  insert tb select 1,'aaaa'   ,0
  union all select 2,'bbbb'   ,0
  union all select 3,'cccc'   ,0
  union all select 4,'aaaa-1'   ,1
  union all select 5,'aaaa-2'   ,1
  union all select 6,'bbbb-1'   ,2
  union all select 7,'cccc-1'   ,3
  union all select 8,'cccc-2'   ,3
  union all select 9,'aaaa-1-1',4
  union all select 10,'cccc-2-1',8
  union all select 11,'cccc-2-2',8
  union all select 12,'aaaa-1-2',4
  go
 
--创建表值函数(对原函数做了修改，增加了前缀)
alter function dbo. f_id()
  returns @re table(id int,parentid int,level int,sid varchar(8000),pre1 varchar(2),pre2 varchar(2))
  as
begin
    declare @l int
    set @l=0
    insert @re select id,parentid,@l,right(10000+id,4),'',''
    from tb where parentid=0
    while @@rowcount>0
    begin
      set @l=@l+1
      insert @re select a.id,a.parentid,@l,b.sid+','+right(10000+a.id,4),'','' 
      from tb a,@re b
      where a.parentid=b.id and b.level=@l-1
    end
update a set pre2=
case
when a.level>1 then case id when (select convert(int,right(max(sid),4)) from @re where parentid=a.parentid and level=a.level) then '└' else '├' end
else case id when (select convert(int,right(max(sid),4)) from @re where level=a.level) then '└' else '├' end
END from @re a where a.level>0
update a set pre1=
CASE
when parentid=(select convert(int,right(max(sid),4)) from @re where level=a.level-1) then '' else '│'
END from @re a where a.level>1
    RETURN 
END

--返回结果集.
select [带缩进的name]=case when b.[level]=0 then a.name
when b.[level]=1 then pre2+replicate('-',4)+a.name
else b.pre1+replicate('',(b.level-1)*4)+b.pre2+replicate('-',4)+a.name
end,a.name,a.id
from tb a,f_id() b where a.id=b.id order by b.sid 

/**//*结果:
带缩进的name             name       id
--------------------               ---------- -----------
aaaa                            aaaa       1
├----aaaa-1                  aaaa-1     4
│├----aaaa-1-1             aaaa-1-1   9
│└----aaaa-1-2             aaaa-1-2   12
├----aaaa-2                  aaaa-2     5
bbbb                            bbbb       2
├----bbbb-1                  bbbb-1     6
cccc                            cccc       3
├----cccc-1                  cccc-1     7
└----cccc-2                  cccc-2     8
├----cccc-2-1               cccc-2-1   10
└----cccc-2-2               cccc-2-2   11
*/
引用资源:http://topic.csdn.net/t/20040904/18/3340610.html
http://www.cnblogs.com/drc/archive/2005/11/04/269062.html

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/zhou__zhou/archive/2007/11/07/1871890.aspx