树型表的设计 上海
drop table tb
go
create table tb(id int,pid int,name varchar(20))
go
insert tb
--/*
select 1,0,'1000'
union all select 2,1,'1001'
union all select 3,1,'1002'
union all select 4,1,'1003'
union all select 5,2,'1004'
union all select 6,2,'1005'
union all select 7,5,'1007'
union all select 8,5,'1008'
union all select 9,7,'1009'
union all select 10,7,'1009'
union all select 11,7,'1010'
union all select 12,10,'1011'
union all select 13,6,'1012'
union all select 14,6,'1013'
union all select 15,8,'1014'
union all select 16,8,'1015'
union all select 17,14,'1016'
--*/
go
go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[t]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[t]
GO
--建一个中间表
CREATE TABLE [dbo].[t] (
[id] [int] NULL ,
[level] [int] NULL ,
[name] [int] NULL ,
[pid] [int] NULL
) ON [PRIMARY]
GO
declare @l int
set @l=0
insert t select id,@l,name,pid
from tb where pid=0
while @@rowcount>0
begin
set @l=@l+1
insert t select a.id,@l,a.name,a.pid
from tb a,t b
where a.pid=b.id and b.level=@l-1
end
go
drop function f_level2name
go
--建函数
create function f_level2name(@id int)
returns int
as
begin
declare @name varchar(20)
declare @level int
declare @pid int
set @level = (select [level] from t where id=@id)
if @level>=2
begin
set @pid=(select pid from t where id=@id)
if @level=2
begin
set @name = (select name from t where id=@id)
end
else
begin
set @name=dbo.f_level2name(@pid)
end
end
return @name
end
go
select *,dbo.f_level2name(id) as level2name from tb
--结果(level2name: id的父节点中level为2对应的name)
id pid name level2name
----------- ----------- -------------------- -----------
1 0 1000 NULL
2 1 1001 NULL
3 1 1002 NULL
4 1 1003 NULL
5 2 1004 1004
6 2 1005 1005
7 5 1007 1004
8 5 1008 1004
9 7 1009 1004
10 7 1009 1004
11 7 1010 1004
12 10 1011 1004
13 6 1012 1005
14 6 1013 1005
15 8 1014 1004
16 8 1015 1004
17 14 1016 1005
（所影响的行数为 17 行）
-------------------------------------------
这函数的效率太低了,当TB表有2000记录时,用30S,(CPU2.1G,256内存)
请教更高效率的写法.
create table tb(id int,pid int,name varchar(20))
insert tb select 1,0,'1000'
union all select 2,1,'1001'
union all select 3,1,'1002'
union all select 4,1,'1003'
union all select 5,2,'1004'
union all select 6,2,'1005'
union all select 7,5,'1007'
union all select 8,5,'1008'
union all select 9,7,'1009'
union all select 10,7,'1009'
union all select 11,7,'1010'
union all select 12,10,'1011'
union all select 13,6,'1012'
union all select 14,6,'1013'
union all select 15,8,'1014'
union all select 16,8,'1015'
union all select 17,14,'1016'
go
--建立level重函数
create function f_id()
returns @re table(id int,level int)
as
begin
declare @l int
set @l=0
insert @re select id,@l
from tb where pid=0
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.id,@l
from tb a,@re b
where a.pid=b.id and b.level=@l-1
end
return
end
go
--建立name函数
create function f_name(@id int,@level int)
returns varchar(20)
as
begin
declare @name varchar(20)
declare @t table(id int identity,name varchar(20))
while @id<>0
begin
select @name=name,@id=pid from tb where id=@id
insert @t values(@name)
end
return((select name from @t where id=(select max(id) from @t)-@level))
end
go
--调用实现查询
select id,level,
level2name=dbo.f_name(id,2),
level3name=dbo.f_name(id,3)
from f_id()
order by id
go
--删除测试
drop function f_id,f_name
drop table tb
/*--测试结果
id level level2name level3name
----------- ----------- -------------------- --------------------
1 0 NULL NULL
2 1 NULL NULL
3 1 NULL NULL
4 1 NULL NULL
5 2 1004 NULL
6 2 1005 NULL
7 3 1004 1007
8 3 1004 1008
9 4 1004 1007
10 4 1004 1007
11 4 1004 1007
12 5 1004 1007
13 3 1005 1012
14 3 1005 1013
15 4 1004 1008
16 4 1004 1008
17 4 1005 1013
（所影响的行数为 17 行）