sql 存储过程层次树形结构

最新推荐文章于 2024-08-11 14:00:50 发布

生财

最新推荐文章于 2024-08-11 14:00:50 发布

阅读量1k

点赞数

文章标签：存储 sql table insert function go

本文链接：https://blog.csdn.net/daonidedie/article/details/2043729

版权

用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