IBM Rational 系统开发最佳实践工具包	WebSphere MQ 最佳实践 TOP 15

化解32层以上嵌套，树型结构的递归实现方法，给大家参考一下！

楼主CrazyFor（冬眠的鼹鼠）2003-01-20 12:39:37 在 MS-SQL Server / 疑难问题提问

前一镇，写了两个递归的树型结构处理函数：
http://expert.csdn.net/Expert/topic/1343/1343007.xml?temp=.730694

后来有朋友提出32层以上嵌套的一个展BOM的实例，没有办法用递归实现，特想了个办法，实现32层以上树型结构的递归方法。现在特将以前的那个函数进行了改进，具体如下：

---------------------------------表及函数脚本

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TreeClass]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TreeClass]
GO

CREATE TABLE [dbo].[TreeClass] (
[TC_id] [int] IDENTITY (1, 1) NOT NULL ,
[TC_PID] [int] NOT NULL ,
[TC_OtherTypeID] [varchar] (8000) COLLATE Chinese_PRC_CI_AS NULL ,
[TC_Name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TreeClass] WITH NOCHECK ADD
CONSTRAINT [PK_TreeClass] PRIMARY KEY CLUSTERED
(
[TC_id]
) ON [PRIMARY]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FN_32GetSubClass]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[FN_32GetSubClass]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FN_32GetTopClass]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[FN_32GetTopClass]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE FUNCTION FN_32GetSubClass (@InputId int,@IdStr varchar(8000)='',@LevelCount int=-1)
/*
参数： @InputId，被搜索子类的ID
@IdStr，一个特殊参数，用于在递归中传数据，注意：调用函数时一定要传入‘’空值
@LevelCount 用于判断是不是递归调用的开始层
*/
RETURNS Varchar(8000)
AS
BEGIN

Declare @TC_ID int,@TC_PID int,@StartLevel int,@Id32 int

if @LevelCount=-1
begin
set @StartLevel=@@NESTLEVEL
set @LevelCount=@StartLevel
end
else
set @StartLevel=-1

If @IdStr='' Set @IdStr=''''+cast(@InputId as varchar)+''''

DECLARE TreeClass CURSOR local FOR --定义游标
SELECT TC_Id,TC_PID
FROM TreeClass
where TC_PID=@InputId

OPEN TreeClass
FETCH NEXT FROM TreeClass
INTO @TC_ID,@TC_PID

WHILE @@FETCH_STATUS = 0 --循环游标，即循环当前类的弟一级子类
BEGIN
select @IdStr=@IdStr+','+''''+cast(@tC_ID as varchar)+''''

if @@NESTLEVEL<32
set @IdStr=dbo.FN_32GetSubClass (@TC_ID,@IdStr,@LevelCount) --递归，自己调用自己。
else
set @IdStr='['+cast(@tC_ID as varchar)+']'+@IdStr

FETCH NEXT FROM TreeClass
INTO @tC_ID,@TC_PID

End

CLOSE TreeClass
DEALLOCATE TreeClass

while @StartLevel=@@NESTLEVEL and charindex(']',@IdStr)>0
begin
set @Id32=substring(@IdStr,2,charindex(']',@IdStr)-2)
set @IdStr=dbo.FN_32GetSubClass (@Id32,@IdStr,@LevelCount)
set @IdStr=replace(@IdStr,'['+cast(@Id32 as varchar)+']','')
end

Return @IdStr

END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE FUNCTION FN_32GetTopClass (@InputId int,@IdStr varchar(8000)='',@type int=0,@LevelCount int=-1)

RETURNS Varchar(8000)
AS
BEGIN

Declare @TC_ID int,@TC_PID int,@StartLevel int,@Id32 int

if @LevelCount=-1
begin
set @StartLevel=@@NESTLEVEL
set @LevelCount=@StartLevel
end
else
set @StartLevel=-1

DECLARE TreeClass CURSOR local FOR
SELECT TC_Id,TC_PID
FROM TreeClass
where TC_ID=@InputId

OPEN TreeClass
FETCH NEXT FROM TreeClass
INTO @TC_ID,@TC_PID

WHILE @@FETCH_STATUS = 0
BEGIN
if @type=1
begin
if @IdStr<>'' select @IdStr=','+@IdStr
select @IdStr=''''+cast(@tC_ID as varchar)+''''+@IdStr
end
else
if @TC_PID=0 select @IdStr=cast(@tC_ID as varchar)

if @@NESTLEVEL<32
select @IdStr=dbo.FN_32GetTopClass (@TC_PID,@IdStr,@type,@LevelCount)
else
set @IdStr=@IdStr+'['+cast(@tC_ID as varchar)+']'

FETCH NEXT FROM TreeClass
INTO @tC_ID,@TC_PID

End

CLOSE TreeClass
DEALLOCATE TreeClass

while @StartLevel=@@NESTLEVEL and charindex(']',@IdStr)>0
begin
set @Id32=substring(@IdStr,charindex('[',@Idstr)+1,charindex(']',@IdStr)-1-charindex('[',@Idstr))
set @IdStr=dbo.FN_32GetTopClass (@Id32,@IdStr,@type,@LevelCount)
set @IdStr=replace(@IdStr,'['+cast(@Id32 as varchar)+']','')
end

Return @IdStr

END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

--------------------------例子表数据，将下面数据写入TXT文档，DTS导入。

TC_id,TC_PID,TC_OtherTypeID,TC_Name
1,0,,宇宙
2,1,,银河系
3,2,,太阳系
4,3,,地球
5,4,,亚洲
6,5,,中国
7,6,,省
8,7,,江苏
9,8,,苏州
10,9,,公园
11,10,,苏州乐园
12,11,,山
13,12,,石头
14,13,,树
15,14,,枝
17,15,,叶
18,17,,虫
21,18,,眼睛
22,21,,眼球
24,22,,泪
25,24,,水
26,25,,氧
28,26,,A
30,28,,B
31,30,,C
32,31,,D
33,32,,E
34,33,,F
35,34,,G
36,35,,H
37,36,,I
38,37,,L
39,38,,M
40,39,,N
41,40,,O
42,41,,P
43,42,,Q
44,43,,R
45,44,,S
46,45,,T
47,46,,U
48,47,,V
49,48,,W
50,49,,X
51,50,,Y
52,51,,Z
53,52,,a
54,53,,b
55,54,,c
56,55,,d
57,56,,e
58,57,,f
59,58,,g
60,59,,h
61,60,,i
62,61,,j
63,62,,k
64,63,,l
65,64,3,m
66,65,,n
67,66,,o
68,67,,p
69,68,,q
70,69,,r
71,70,,s
72,71,,t
73,72,,u
74,73,,v
75,74,,w
76,75,,x
77,76,,y
78,77,,z

---------------------------应用实例
--得到ID为1的所有下层类别ID串
declare @aa varchar(8000)
set @aa=dbo.FN_32GetSubClass(1,default,default)
print @aa

--查询ID为1的所有下层记录

declare @aa varchar(8000)
set @aa=dbo.FN_32GetSubClass(1,default,default)
print @aa
exec('SELECT * from treeclass where TC_id in ('+ @aa +')')

方法二，
select * from treeclass where charindex(''''+cast(TC_id as varchar)+'''',dbo.fn_32getsubclass(1,default,default))>0

方法二有一个问题，在我这边运行非常的慢，不知道为什么，有没有高手解释一下，谢谢！！

--得到ID为78顶层ID

declare @aa varchar(8000)
set @aa=dbo.fn_32gettopclass(78,default,default,default)
print @aa

--得到提供ID：78所在枝的所有ID
declare @aa varchar(8000)
set @aa=dbo.FN_32GetSubClass(dbo.fn_32gettopclass(78,default,default,default),default,default)
print @aa

--得到当前ID到顶层的ID串
declare @aa varchar(8000)
set @aa=dbo.fn_32gettopclass(78,default,1,default)
print @aa

---按照树顺序排序，
select * from treeclass order by dbo.fn_gettopclass(tc_id,'',1)
原来的函数可以实现，现在的函数不行，因为32以后的ID不是连续的在返回结果中，现在是，先得到所有32层以前的，再逐个处理超过32层的分枝，所以需要这个功能的话需要适当修改函数，当然我想是很容易实现的。

问题点数：20、回复次数：28Top

1 楼leimin（黄山光明顶）回复于 2003-01-20 12:46:00 得分 5

收藏先，下午有时间在看。Top

2 楼CABO（腊肉）回复于 2003-01-20 13:08:35 得分 5

CrazyFor (蚂蚁)
这种致富不忘穷兄弟的做法值得鼓励！Top

3 楼CrazyFor（冬眠的鼹鼠）回复于 2003-01-20 13:14:20 得分 0

CABO(白椒腊肉)

呵呵~~~多谢！Top

4 楼pengdali（）回复于 2003-01-20 13:14:48 得分 5

学习！Top

5 楼Lostinet（Hello）回复于 2003-01-20 13:30:41 得分 5

http://expert.csdn.net/Expert/TopicView2.asp?id=962569&datebasetype=now

BuildRelation:(555,里面几个递归来重建ItemsRelation,但是我的网站进不去了。。)
http://www.lostinet.com/temp/ItemsRelation.sql
Top

6 楼springgwn（hana）回复于 2003-01-20 15:56:23 得分 0

请问，您这里有没有关于树图结构如何显示的方法或者例子，我曾经贴过一贴，可是无人响应，您这里提的存储和查询方法都不错，值得学习、研究。树图对我来说，现在是个非常头疼的问题，特别是关于树图的显示上，我们采用的是js代码编的，无论是显示的速度上，还是使用上都达不到要求，现向您提出援助，如果有这方面的信息，还望能和我联系（hblg_gwn@sina.com），非常感谢。Top

7 楼springgwn（hana）回复于 2003-01-20 15:58:26 得分 0

应当是“请求援助”！！！Top

8 楼happydreamer（www.sz.js.cn,www.gyxk.com）回复于 2003-01-20 16:03:42 得分 0

copy copyTop

9 楼tomhuang（春城）回复于 2003-01-20 16:10:01 得分 0

不错，收藏：）Top

10 楼CrazyFor（冬眠的鼹鼠）回复于 2003-01-20 16:16:03 得分 0

你是要哪样的树图?
1,
地球
|_______________
| | |
亚洲非洲美洲
|____
| |
中国日本
|____
| |
省自治区

2
-地坏
+非洲
-亚洲
-中国
+省
+自治区
+日本
+美洲
Top

11 楼springgwn（hana）回复于 2003-01-20 16:55:06 得分 0

第二种。谢谢！！Top

12 楼CrazyFor（冬眠的鼹鼠）回复于 2003-01-21 09:35:53 得分 0

springgwn()

你是用在什么地方？应该程序中吗？Top

13 楼j9988（j9988）回复于 2003-01-21 09:48:19 得分 0

MARK!Top

14 楼springgwn（hana）回复于 2003-01-21 10:29:00 得分 0

我用在web程序中，需要在网页上显示，希望能得到您的帮助！Top

15 楼CrazyFor（冬眠的鼹鼠）回复于 2003-01-21 10:37:54 得分 0

这个问题我也想过好久，要不把数据写入XML中再处理，要不然就是用JS来做，还有就是点一个加号就刷新一下窗体。Top

16 楼CrazyFor（冬眠的鼹鼠）回复于 2003-01-21 10:40:00 得分 0

不知道其它人有没有更好的办法，拿出来给大家参考一下吧。：）Top

17 楼sky_blue（蓝天2007）回复于 2003-01-21 10:50:07 得分 0

create table new_tree ( parent varchar(80), child varchar(80),weight int)
go
insert new_tree values ( '1','2',3);
insert new_tree values ( '1','3',4);
insert new_tree values ( '2','4',3);
insert new_tree values ( '2','5',5);
insert new_tree values ( '3','6',7);
insert new_tree values ( '3','7',3);
insert new_tree values ( '3','8',2);
insert new_tree values ( '6','9',1);
insert new_tree values ( '5','10',4);
insert new_tree values ( '4','11',3);
insert new_tree values ( '9','12',7);
insert new_tree values ( '7',NULL,1);
insert new_tree values ( '8',NULL,4);
insert new_tree values ( '10',NULL,3);
insert new_tree values ( '11',NULL,3);
insert new_tree values ( '12',NULL,7);

广度优先：
create proc proc_new_tree (@parent varchar(80),@mode int =0)
as

begin
set nocount on
declare @level int
declare @tmp1 table ( parent varchar(80), child varchar(80),level int)
select @level =1
insert @tmp1 select parent,child,@level from new_tree where parent = @parent
while exists(select * from @tmp1 where child is not NULL and level=@level)
begin
insert @tmp1 select a.parent,a.child ,@level+1 from new_tree a,@tmp1 b where a.parent = b.child and b.level=@level
select @level=@level +1
end
if @mode =0 select * from @tmp1
else select * from @tmp1 where child is null
set nocount off
end
go

深度优先：
create proc proc__tree (@parent char(20))
as
begin
set nocount on
declare @level int ,@i int ,@flag int
declare @stack table (parent char(20),child char(20),level int,row int , flag int)
select @level = 1,@i=1,@flag=1
insert @stack select parent,child, @level,0,1 from new_tree where parent = @parent and child is not null

while @level > 0
begin
if exists (select * from @stack where level = @level and flag=1)
begin
select @parent = min(child) from @stack where level = @level and flag=1
update @stack set flag =0 , row=@i where level = @level and child = @parent and flag =1
select @i = @i +1
insert @stack select parent,child, @level + 1,0,1 from new_tree where parent = @parent and child is not null
if @@rowcount > 0
select @level = @level + 1
end
else
begin
select @level = @level - 1
end
end
select row,parent ,child,level from @stack order by row
set nocount off
end
go
proc__tree '1'
Top

18 楼Haiwer（海阔天空）回复于 2003-01-21 14:04:44 得分 0

好动动，收藏！
Top

19 楼xlhl（顽皮兔子）回复于 2003-01-21 15:46:31 得分 0

收藏!!Top

20 楼CrazyFor（冬眠的鼹鼠）回复于 2003-01-23 17:35:18 得分 0

---按照树顺序排序方法

select *,dbo.fn_32gettopclass(Tc_id,default,1,default) from treeclass order by dbo.fn_32gettopclass(Tc_id,default,1,default)
Top

21 楼CrazyFor（冬眠的鼹鼠）回复于 2003-02-20 16:23:42 得分 0

更新：

CREATE FUNCTION FN_32GetTopClass (@InputId int,@IdStr varchar(8000)='',@type int=0,@LevelCount int=-1)
/*
@Type= 0:得到顶层ID
1:得到当前到顶层的串
2:排序时使用
*/

RETURNS Varchar(8000)
AS
BEGIN

Declare @TC_ID int,@TC_PID int,@StartLevel int,@Id32 int,@OrderStr varchar(10)

if @LevelCount=-1
begin
set @StartLevel=@@NESTLEVEL
set @LevelCount=@StartLevel
end
else
set @StartLevel=-1

DECLARE TreeClass CURSOR local FOR
SELECT TC_Id,TC_PID
FROM TreeClass
where TC_ID=@InputId

OPEN TreeClass
FETCH NEXT FROM TreeClass
INTO @TC_ID,@TC_PID

WHILE @@FETCH_STATUS = 0
BEGIN

if @type=1 or @type=2
begin
if @type=2 set @OrderStr='0000000000' else set @OrderStr=''
if @IdStr<>'' select @IdStr=','+@IdStr
select @IdStr=''''+right(@OrderStr+cast(@tC_ID as varchar),10)+''''+@IdStr
end
else
if @TC_PID=0 select @IdStr=cast(@tC_ID as varchar)

if @@NESTLEVEL<32
select @IdStr=dbo.FN_32GetTopClass (@TC_PID,@IdStr,@type,@LevelCount)
else
set @IdStr=@IdStr+'['+cast(@tC_ID as varchar)+']'

FETCH NEXT FROM TreeClass
INTO @tC_ID,@TC_PID

End

CLOSE TreeClass
DEALLOCATE TreeClass

while @StartLevel=@@NESTLEVEL and charindex(']',@IdStr)>0
begin
set @Id32=substring(@IdStr,charindex('[',@Idstr)+1,charindex(']',@IdStr)-1-charindex('[',@Idstr))
set @IdStr=dbo.FN_32GetTopClass (@Id32,@IdStr,@type,@LevelCount)
set @IdStr=replace(@IdStr,'['+cast(@Id32 as varchar)+']','')
end

Return @IdStr

END

Top

22 楼zeng_zhh（紫河）回复于 2003-02-28 16:41:26 得分 0

我作個標記.好東東.Top

23 楼jin888888（洒打摸）回复于 2003-05-14 11:48:23 得分 0

收藏！好动动Top

24 楼happyforevery（天天向上）回复于 2003-05-24 15:02:11 得分 0

markTop

25 楼nik_Amis（...）回复于 2003-06-02 10:08:11 得分 0

super upTop

26 楼MAGXJ500T（滚龙）回复于 2003-08-11 15:37:44 得分 0

一个问题：

如果返回的ID数量较多，超过8000怎么办？Top

27 楼laker_tmj（laker）回复于 2003-08-21 15:29:30 得分 0

upTop

28 楼eastpond（东塘）回复于 2003-08-26 16:56:33 得分 0

收藏Top