</TR> <TR> <TH>名称</TH> <TD>方法1:父子结构Tree1表</TD> <TD>方法2:编码结构Tree2表</TD> </TR> <TR> <TH>数据形式</TH> <TD> <xmp> id pId content -------------------------------------- 1 0 节点 2 0 节点 ... 11 1 节点 ... 21 2 节点 ... 121 12 节点 ... 1110 110 节点 </xmp> </TD> <TD> <xmp> id nodeCode content -------------------------------------- 1 001 节点 2 002 节点 ... 11 001001 节点 ... 112 001001002 节点 ... 1104 010010004 节点 ... </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 + @EE)),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> id content 深度 1 节点 1 11 节点 2 111 节点 3 .. 120 节点 3 12 节点 2 ... 1072 节点 3 </xmp> </TD> <TD> <xmp>id nodeCode content 深度 1 001 节点 1 11 001001 节点 2 111 001001001 节点 3 ... 210 001010010 节点 3 2 002 节点 1 21 002001 节点 2 211 002001001 节点 3 ... 1088 010008008 节点 3 ... 1101 010010001 节点 3 </xmp> </TD> </TR> <TR> <TH>查询时间</TH> <TD> <xmp> 30毫秒左右 </xmp> </TD> <TD> <xmp>15毫秒左右 </xmp> </TD> </TR> </TABLE>
下边的存成HTML看<HEAD>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<style>
body,td,th
{
font-size:12px;
vertical-align:top;
}
th
{
layout-flow:vertical-ideographic ;
}
</style>
</HEAD>
<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>
id pId content
--------------------------------------
1 0 节点
2 0 节点
...
11 1 节点
...
21 2 节点
...
121 12 节点
...
1110 110 节点
</xmp>
</TD>
<TD>
<xmp>
id nodeCode content
--------------------------------------
1 001 节点
2 002 节点
...
11 001001 节点
...
112 001001002 节点
...
1104 010010004 节点
...
</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 + @EE)),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>
id content 深度
1 节点 1
11 节点 2
111 节点 3
..
120 节点 3
12 节点 2
...
1072 节点 3
</xmp>
</TD>
<TD>
<xmp>id nodeCode content 深度
1 001 节点 1
11 001001 节点 2
111 001001001 节点 3
...
210 001010010 节点 3
2 002 节点 1
21 002001 节点 2
211 002001001 节点 3
...
1088 010008008 节点 3
...
1101 010010001 节点 3
</xmp>
</TD>
</TR>
<TR>
<TH>查询时间</TH>
<TD>
<xmp>
30毫秒左右
</xmp>
</TD>
<TD>
<xmp>15毫秒左右
</xmp>
</TD>
</TR>
</TABLE>