建表:
CREATE TABLE [dbo].[test](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ID] [int] NOT NULL,
[Name] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[Pid] [int] NOT NULL
) ON [PRIMARY]
添加数据:
INSERT INTO [test]([ID],[Name],[Pid])VALUES(1,a,0)
INSERT INTO [test]([ID],[Name],[Pid])VALUES(2,b,1)
INSERT INTO [test]([ID],[Name],[Pid])VALUES(3,c,1)
INSERT INTO [test]([ID],[Name],[Pid])VALUES(4,d,2)
INSERT INTO [test]([ID],[Name],[Pid])VALUES(5,e,4)
一个常见的树,给定一个节点,要找出所有的父节点或子节点
在ORACEL里有 start with...connect by,这个网上很多例子
在SQL2005里增加了WITH common_table_expression (Transact-SQL) 这样的东东,在联机帮助里"公用表表达式 (CTE)" 或"递归查询"可以找到,也有例子.
如果是在SQL2000里,好象还没有特别好的办法,除了写函数和存储过程.
这是查所有父节点名字的sp
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[search] @stationname nvarchar(100),
@returnstr nvarchar(4000) output AS
declare @stationid int
declare @stationpid int
select @stationid=stationid,@stationname=stationname,@stationpid=stationpid from test where stationname=@stationname
set @returnstr=@stationname
while @stationpid<>0
begin
select @stationid=stationid,@stationname=stationname,@stationpid=stationpid from test where stationid=@stationpid
set @returnstr= @stationname+'|'+@returnstr
set @stationid=@stationpid
end
return @Returnstr
这是查所有子节点的函数
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER function [dbo].[f_getChild](@ID VARCHAR(10))
returns @t table(ID VARCHAR(10),PID VARCHAR(10),Level INT)
as
begin
declare @i int,@ret varchar(8000)
set @i = 1
insert into @t select stationid,stationpid,@i from test where stationpid= @ID
while @@rowcount<>0
begin
set @i = @i + 1
insert into @t
select
a.stationid,a.stationpid,@i
from
test a,@t b
where
a.stationpid=b.stationid and b.Level = @i-1
end
return
end
