sql 存储过程 层次 树形结构

用SQL存储过程生成树形结构数据表。

建立表：

create table table_NewsClass
(NewsclassName varchar(50),
 NewsClassID int,NewsClassParentID int
)

insert into table_NewsClass
select '顶级栏目',  1,     0  union all
select '栏目一' , 2,     1 union all
select '栏目二'  ,3,     1 union all
select '栏目三', 4,     2 union all
select 'SFG001' ,5,     2 union all
select 'SFG002' ,6,     3 union all
select 'SFG002' ,7,     3 union all
select 'SFG002' ,8,     2 union all
select 'SFG003' ,9,     3 union all
select 'WIP001' ,10,     2 union all
select 'WIP001' ,11  ,   2 union all
select 'WIP002' ,12  ,   3 union all
select 'WIP003' ,22 ,   1 union all
select 'WIP003' ,23  ,   1 union all
select 'RAW001',21,      25 union all
select 'RAW004',23,      4 union all
select 'KKK001',25,      23

 

 

建立函数

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

--返回指定树形结构表
ALTER function [dbo].[F_GetTree](@parent int)
returns @t table(NewsClassName nvarchar(50),parent int,child int,level int,sort nvarchar(1000) collate Latin1_General_BIN)
as
begin
 declare @level int
     set @level=1
     insert into @t
     select NewsClassName,NewsClassParentID,NewsClassID ,@level,CONVERT(nvarchar(10),NewsClassParentID)+CONVERT(nvarchar(10),NewsClassID)
     from table_newsclass
     where CONVERT(nvarchar(10),NewsClassParentID)=CONVERT(nvarchar(10),@parent) collate Latin1_General_BIN
     while @@rowcount>0
     begin
set @level=@level+1
         insert @t
         select a.newsClassName,a.newsclassparentid,a.newsclassid,@level,b.sort+'-'+CONVERT(nvarchar(10),NewsClassID)
         from   table_Newsclass a ,@t b
         where CONVERT(nvarchar(10),a.newsclassparentid)=CONVERT(nvarchar(10),b.child)  collate Latin1_General_BIN and b.level=@level-1
    end
return
end

 

调用函数

 select child as  NewsClassID,
    space(level*2)+'|--' + NewsClassName as NewsClassname  from dbo.F_GetTree(0) order by sort

返回结果

1   |--顶级栏目
2     |--栏目一
22     |--WIP003
10       |--WIP001
11       |--WIP001
4       |--栏目三
23         |--RAW004
25           |--KKK001
21             |--RAW001
5       |--SFG001
8       |--SFG002
23     |--WIP003
25       |--KKK001
21         |--RAW001
3     |--栏目二
12       |--WIP002
6       |--SFG002
7       |--SFG002
9       |--SFG003

是你想要的结果么？

嘿嘿！