树型结构数据的存储采用:
Tree(ID,ParentID,Re)
如果仅对于存储来讲,无疑是最经济!
但是利用这样的结构,来提供一些基于稍微复杂点的查询的应用表现形式
效率应该说相当低下!
如: 查询某节点的路径等!
如要高效的查询,我们可以在维护数据时下点功夫!
我们以一个树型结构论坛的实现为例:Tree(ID,ParentID,RootID,OrderID,MaxID,Indent,Title,Content,Re)
ID: Integer 帖子ID
ParentID: Integer 父贴ID
RootID: Integer 根帖ID
OrderID: Integer 同一个根帖中,帖子顺序ID
MaxID: Integer 用于使新贴在顶部
Indent: Integer 缩进量
Title: Varchar 帖子标题
Content: Varchar 帖子内容
Re: Varchar 除 ID,ParentID 外的贴子线索这样的设计只要维护好每一个字段都为查询显示提高了效率!
请看下面的维护程序:
--==========================================
alter procedure AppSP_AddNew
@ID integer
,@Title varchar(8000) =null
,@Content varchar(8000)=null
as
--declare @id int
--set @id=0
if @ID=0
begin
insert into Tree (ParentID,OrderID,Indent,Title,Content)
values (0,0,0,@Title,@Content)
--把帖子顶到上面:
update Tree
set RootID = ID
,MaxId = (select max(id) from Tree)
where RootID is null
end
else
begin
--调整同一个"根帖"中,帖子的内部顺序:
update Tree
set OrderID = OrderID + 1
where RootID = (select rootid
from tree
where ID = @id)
and OrderID > (select OrderID
from Tree
where ID = @id
)
--插入回复的帖子,同时维护 RootID,ParentID,OrderID,Indent,re,Title,Content
insert into Tree (RootID,ParentID,OrderID,Indent,re,Title,Content)
select RootID,@ID,OrderID+1,Indent + 1
,case when re is null then cast(parentid as varchar)
else re + '-' + cast(parentid as varchar)
end
,isnull(@Title,'Re: ' + Title),@Content
from Tree
where id=@id
--把帖子顶到上面:
update Tree
set maxid = (select max(id)
from Tree
)
where rootid = (select rootid
from tree
where id=@id
)
end
--========================================
Tree(ID,ParentID,Re)
如果仅对于存储来讲,无疑是最经济!
但是利用这样的结构,来提供一些基于稍微复杂点的查询的应用表现形式
效率应该说相当低下!
如: 查询某节点的路径等!
如要高效的查询,我们可以在维护数据时下点功夫!
我们以一个树型结构论坛的实现为例:Tree(ID,ParentID,RootID,OrderID,MaxID,Indent,Title,Content,Re)
ID: Integer 帖子ID
ParentID: Integer 父贴ID
RootID: Integer 根帖ID
OrderID: Integer 同一个根帖中,帖子顺序ID
MaxID: Integer 用于使新贴在顶部
Indent: Integer 缩进量
Title: Varchar 帖子标题
Content: Varchar 帖子内容
Re: Varchar 除 ID,ParentID 外的贴子线索这样的设计只要维护好每一个字段都为查询显示提高了效率!
请看下面的维护程序:
--==========================================
alter procedure AppSP_AddNew
@ID integer
,@Title varchar(8000) =null
,@Content varchar(8000)=null
as
--declare @id int
--set @id=0
if @ID=0
begin
insert into Tree (ParentID,OrderID,Indent,Title,Content)
values (0,0,0,@Title,@Content)
--把帖子顶到上面:
update Tree
set RootID = ID
,MaxId = (select max(id) from Tree)
where RootID is null
end
else
begin
--调整同一个"根帖"中,帖子的内部顺序:
update Tree
set OrderID = OrderID + 1
where RootID = (select rootid
from tree
where ID = @id)
and OrderID > (select OrderID
from Tree
where ID = @id
)
--插入回复的帖子,同时维护 RootID,ParentID,OrderID,Indent,re,Title,Content
insert into Tree (RootID,ParentID,OrderID,Indent,re,Title,Content)
select RootID,@ID,OrderID+1,Indent + 1
,case when re is null then cast(parentid as varchar)
else re + '-' + cast(parentid as varchar)
end
,isnull(@Title,'Re: ' + Title),@Content
from Tree
where id=@id
--把帖子顶到上面:
update Tree
set maxid = (select max(id)
from Tree
)
where rootid = (select rootid
from tree
where id=@id
)
end
--========================================
AS
BEGIN Declare @TC_ID int,@TC_PID intDECLARE TreeClass CURSOR local FOR
SELECT TC_Id,TC_PID
FROM TreeClass
where TC_ID=@InputIdOPEN TreeClass
FETCH NEXT FROM TreeClass
INTO @TC_ID,@TC_PIDWHILE @@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) select @IdStr=dbo.FN_GetTopClass (@TC_PID,@IdStr,@type)
FETCH NEXT FROM TreeClass
INTO @tC_ID,@TC_PIDEndCLOSE TreeClass
DEALLOCATE TreeClassReturn @IdStrEND
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO例:
--得到ID为1的所有下层类别ID串
select dbo.fn_getsubclass(1,'')
--查询ID为1的所有下层记录
select * from treeclass where charindex(''''+cast(TC_id as varchar)+'''',dbo.fn_getsubclass(1,''))>0
--得到ID为10顶层ID
select dbo.fn_gettopclass(10,'',0)
--得到提供ID所在枝的所有ID
select dbo.fn_getsubclass(dbo.fn_gettopclass(10,'',0),'')
--得到当前ID到顶层的ID串
select dbo.fn_gettopclass(10,'',1)
前一镇,写了两个递归的树型结构处理函数:
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]
GOCREATE 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]
GOALTER 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]
GOif 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]
GOSET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GOCREATE 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 intif @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=@InputIdOPEN TreeClass
FETCH NEXT FROM TreeClass
INTO @TC_ID,@TC_PIDWHILE @@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_PIDEndCLOSE TreeClass
DEALLOCATE TreeClasswhile @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)+']','')
endReturn @IdStrEND
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
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=-1DECLARE TreeClass CURSOR local FOR
SELECT TC_Id,TC_PID
FROM TreeClass
where TC_ID=@InputIdOPEN TreeClass
FETCH NEXT FROM TreeClass
INTO @TC_ID,@TC_PIDWHILE @@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_PIDEndCLOSE TreeClass
DEALLOCATE TreeClasswhile @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)+']','')
endReturn @IdStrENDGO
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顶层IDdeclare @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层的分枝,所以需要这个功能的话需要适当修改函数,当然我想是很容易实现的。
更新: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=-1DECLARE TreeClass CURSOR local FOR
SELECT TC_Id,TC_PID
FROM TreeClass
where TC_ID=@InputIdOPEN TreeClass
FETCH NEXT FROM TreeClass
INTO @TC_ID,@TC_PIDWHILE @@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_PIDEndCLOSE TreeClass
DEALLOCATE TreeClasswhile @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)+']','')
endReturn @IdStrEND
你是常熟人吗?好像和我一个中学同学同名啊。