</TR> <TR> <TH>名称</TH> <TD>方法1:父子结构Tree1表</TD> <TD>方法2:编码结构Tree2表</TD> </TR> <TR> <TH>数据形式</TH> <TD> <xmp> idpIdcontent -------------------------------------- 10节点 20节点 ... 111节点 ... 212节点 ... 12112节点 ... 1110110节点 </xmp> </TD> <TD> <xmp> idnodeCodecontent -------------------------------------- 1001节点 2002节点 ... 11001001节点 ... 112001001002节点 ... 1104010010004节点 ... </xmp> </TD> </TR><TR> <TH>建表语句</TH> <TD> <xmp> CREATE TABLE [dbo].[Tree1] ( [id] [int] IDENTITY (1, 1) NOT NULL , [pId] [int] NULL , [content] [varchar] (10) NULL ) ON [PRIMARY] </xmp> </TD> <TD> <xmp> CREATE TABLE [dbo].[Tree2] ( [id] [int] IDENTITY (1, 1) NOT NULL , [nodeCode] [varchar] (120) NOT NULL , [content] [varchar] (10) NULL ) ON [PRIMARY] </xmp> </TD> </TR> <TR> <TH>插入数据10+100+1000条,深度3,广度10</TH> <TD> <xmp>--truncate table Tree1 declare @E int--广度循环变量 declare @EE int--广度循环变量 declare @EEE int--广度循环变量 declare @pId int--父id declare @content varchar(10)set @content='节点' --添加第1层10个节点 set @E=1 while @E<=10 begin set @pId=0 insert tree1(pId,content) values(@pId,@content) set @E=@E+1 end --添加第2层100个节点 set @E=1 while @E<=10 begin set @EE=1 while @EE<=10 begin set @pId=@E insert tree1(pId,content) values(@pId,@content) set @EE=@EE+1 end set @E=@E+1 end --添加第3层1000个节点 set @E=1 while @E<=10 begin set @EE=1 while @EE<=10 begin set @EEE=1 while @EEE<=10 begin set @pId=@E*10+ @EE insert tree1(pId,content) values(@pId,@content) set @EEE=@EEE+1 end set @EE=@EE+1 end set @E=@E+1 end --select count(*) from tree1</xmp></TD> <TD> <xmp>--truncate table Tree2 declare @nodeCode varchar(30) declare @content varchar(10) declare @E int--广度循环变量 declare @EE int--广度循环变量 declare @EEE int--广度循环变量 set @content='节点'--添加第1层10个节点 set @E=1 while @E<= 10 begin set @nodeCode =right(rtrim(str(1000 + @E)),3) insert Tree2(nodeCode,content) values(@nodeCode,@content) set @E=@E+1 end --添加第2层100个节点 set @E=1 while @E<= 10 begin set @EE=1 while @EE<= 10 begin set @nodeCode =right(rtrim(str(1000 + @E)),3) set @nodeCode =@nodeCode + right(rtrim(str(1000 + @EE)),3) insert Tree2(nodeCode,content) values(@nodeCode,@content) set @EE=@EE+1 end set @E=@E+1 end --添加第3层1000个节点 set @E=1 while @E<= 10 begin set @EE=1 while @EE<= 10 begin set @EEE=1 while @EEE<= 10 begin set @nodeCode =right(rtrim(str(1000 + @E)),3) set @nodeCode =@nodeCode + right(rtrim(str(1000 + @EEE)),3) insert Tree2(nodeCode,content) values(@nodeCode,@content) set @EEE=@EEE+1 end set @EE=@EE+1 end set @E=@E+1 end select count(*) from tree2</xmp> </TD> </TR> <TR> <TH>得到节点深度和排序的办法</TH> <TD> <xmp> 用函数,本例是邹建写的,效率比较高。 create function get_Deep_tree1() returns @re table([id] int,[level] int,sid varchar(8000)) as begin declare @l int set @l=0 insert @re select [id],@l,right(10000+[id],4) from [tree1] where [pid]=0 while @@rowcount>0 begin set @l=@l+1 insert @re select a.[id],@l,b.sid+right(10000+a.[id],4) from [tree1] a,@re b where a.[pid]=b.[id] and b.[level]=@l-1 end return end go </xmp> </TD> <TD> <xmp> 节点深度就是编码长度/3 len(nodeCode)/3 排序只要order by nodeCode asc就可以得到 web叶面需要的 </xmp> </TD> </TR><TH>查询排序</TH> <TD> <xmp> select a.id,a.content,b.[level]+1 深度 from [tree1] a,get_Deep_tree1() b where a.[id]=b.[id] order by b.sid </xmp> </TD> <TD> <xmp>select *,len(nodeCode)/3 深度 from tree2 order by nodeCode asc </xmp> </TD> </TR> <TR> <TH>排序结果</TH> <TD> <xmp> idcontent 深度 1节点1 11节点2 111节点3 .. 120节点3 12节点2 ... 1072节点3 </xmp> </TD> <TD> <xmp>idnodeCode content深度 1001节点1 11001001节点2 111001001001节点3 ... 210001010010节点3 2002节点1 21002001节点2 211002001001节点3 ... 1088010008008节点3 ... 1101010010001节点3 </xmp> </TD> </TR> <TR> <TH>查询时间</TH> <TD> <xmp> 30毫秒左右 </xmp> </TD> <TD> <xmp>15毫秒左右 </xmp> </TD> </TR> </TABLE>
body,td,th
{
font-size:12px;
vertical-align:top;
}
th
{
layout-flow:vertical-ideographic ;
}
</style><TABLE border=1>
<TR>
<TD colspan=3 align=center>两种树型结构SQL的对比.htm</TD>
</TR>
<TR>
<TH>名称</TH>
<TD>方法1:父子结构Tree1表</TD>
<TD>方法2:编码结构Tree2表</TD>
</TR>
<TR>
<TH>数据形式</TH>
<TD>
<xmp>
idpIdcontent
--------------------------------------
10节点
20节点
...
111节点
...
212节点
...
12112节点
...
1110110节点
</xmp>
</TD>
<TD>
<xmp>
idnodeCodecontent
--------------------------------------
1001节点
2002节点
...
11001001节点
...
112001001002节点
...
1104010010004节点
...
</xmp>
</TD>
</TR><TR>
<TH>建表语句</TH>
<TD>
<xmp>
CREATE TABLE [dbo].[Tree1] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[pId] [int] NULL ,
[content] [varchar] (10) NULL
) ON [PRIMARY]
</xmp>
</TD>
<TD>
<xmp>
CREATE TABLE [dbo].[Tree2] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[nodeCode] [varchar] (120) NOT NULL ,
[content] [varchar] (10) NULL
) ON [PRIMARY]
</xmp>
</TD>
</TR>
<TR>
<TH>插入数据10+100+1000条,深度3,广度10</TH>
<TD>
<xmp>--truncate table Tree1
declare @E int--广度循环变量
declare @EE int--广度循环变量
declare @EEE int--广度循环变量
declare @pId int--父id
declare @content varchar(10)set @content='节点'
--添加第1层10个节点
set @E=1
while @E<=10
begin
set @pId=0
insert tree1(pId,content) values(@pId,@content)
set @E=@E+1
end
--添加第2层100个节点
set @E=1
while @E<=10
begin
set @EE=1
while @EE<=10
begin
set @pId=@E
insert tree1(pId,content) values(@pId,@content)
set @EE=@EE+1
end
set @E=@E+1
end
--添加第3层1000个节点
set @E=1
while @E<=10
begin
set @EE=1
while @EE<=10
begin
set @EEE=1
while @EEE<=10
begin
set @pId=@E*10+ @EE
insert tree1(pId,content) values(@pId,@content)
set @EEE=@EEE+1
end
set @EE=@EE+1
end
set @E=@E+1
end
--select count(*) from tree1</xmp></TD>
<TD>
<xmp>--truncate table Tree2
declare @nodeCode varchar(30)
declare @content varchar(10)
declare @E int--广度循环变量
declare @EE int--广度循环变量
declare @EEE int--广度循环变量
set @content='节点'--添加第1层10个节点
set @E=1
while @E<= 10
begin
set @nodeCode =right(rtrim(str(1000 + @E)),3)
insert Tree2(nodeCode,content) values(@nodeCode,@content)
set @E=@E+1
end
--添加第2层100个节点
set @E=1
while @E<= 10
begin
set @EE=1
while @EE<= 10
begin
set @nodeCode =right(rtrim(str(1000 + @E)),3)
set @nodeCode =@nodeCode + right(rtrim(str(1000 + @EE)),3)
insert Tree2(nodeCode,content) values(@nodeCode,@content)
set @EE=@EE+1
end
set @E=@E+1
end
--添加第3层1000个节点
set @E=1
while @E<= 10
begin
set @EE=1
while @EE<= 10
begin
set @EEE=1
while @EEE<= 10
begin
set @nodeCode =right(rtrim(str(1000 + @E)),3)
set @nodeCode =@nodeCode + right(rtrim(str(1000 + @EEE)),3)
insert Tree2(nodeCode,content) values(@nodeCode,@content)
set @EEE=@EEE+1
end
set @EE=@EE+1
end
set @E=@E+1
end
select count(*) from tree2</xmp>
</TD>
</TR>
<TR>
<TH>得到节点深度和排序的办法</TH>
<TD>
<xmp>
用函数,本例是邹建写的,效率比较高。
create function get_Deep_tree1()
returns @re table([id] int,[level] int,sid varchar(8000))
as
begin
declare @l int
set @l=0
insert @re select [id],@l,right(10000+[id],4)
from [tree1] where [pid]=0
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.[id],@l,b.sid+right(10000+a.[id],4)
from [tree1] a,@re b
where a.[pid]=b.[id] and b.[level]=@l-1
end
return
end
go
</xmp>
</TD>
<TD>
<xmp>
节点深度就是编码长度/3
len(nodeCode)/3
排序只要order by nodeCode asc就可以得到
web叶面需要的
</xmp>
</TD>
</TR><TH>查询排序</TH>
<TD>
<xmp>
select a.id,a.content,b.[level]+1 深度
from [tree1] a,get_Deep_tree1() b
where a.[id]=b.[id]
order by b.sid
</xmp>
</TD>
<TD>
<xmp>select *,len(nodeCode)/3 深度 from tree2 order by nodeCode asc
</xmp>
</TD>
</TR>
<TR>
<TH>排序结果</TH>
<TD>
<xmp>
idcontent 深度
1节点1
11节点2
111节点3
..
120节点3
12节点2
...
1072节点3
</xmp>
</TD>
<TD>
<xmp>idnodeCode content深度
1001节点1
11001001节点2
111001001001节点3
...
210001010010节点3
2002节点1
21002001节点2
211002001001节点3
...
1088010008008节点3
...
1101010010001节点3
</xmp>
</TD>
</TR>
<TR>
<TH>查询时间</TH>
<TD>
<xmp>
30毫秒左右
</xmp>
</TD>
<TD>
<xmp>15毫秒左右
</xmp>
</TD>
</TR>
</TABLE>
http://www.cnblogs.com/renyu732/archive/2005/09/27/244735.html