create table tb ( id int identity(5,1), sb_1 int, sb_2 int ) insert into tb select 23,24 union all select 23,25 union all select 24,32 union all select 32,26 union all select 26,33 go--查找所有父结点及子节点的问题,当前假设sb_1为父结点,sb_2为子结点建立函数,查找所有子结点 CREATE FUNCTION f_Pid(@sb_1 int) RETURNS @t_Level TABLE(sb_1 int,Level int) AS BEGIN DECLARE @Level int SET @Level=1 INSERT @t_Level SELECT @sb_1,@Level WHILE @@ROWCOUNT>0 BEGIN SET @Level=@Level+1 INSERT @t_Level SELECT a.sb_2,@Level FROM tb a,@t_Level b WHERE a.sb_1=b.sb_1 AND b.Level=@Level-1 END RETURN END GOselect * from dbo.f_Pid(23)sb_1 Level ----------- ----------- 23 1 24 2 25 2 32 3 26 4 33 5(6 行受影响) 如要查子结点,在函数中做相应操作即可
(
id int identity(5,1),
sb_1 int,
sb_2 int
)
insert into tb
select 23,24 union all
select 23,25 union all
select 24,32 union all
select 32,26 union all
select 26,33
go--查找所有父结点及子节点的问题,当前假设sb_1为父结点,sb_2为子结点建立函数,查找所有子结点
CREATE FUNCTION f_Pid(@sb_1 int)
RETURNS @t_Level TABLE(sb_1 int,Level int)
AS
BEGIN
DECLARE @Level int
SET @Level=1
INSERT @t_Level SELECT @sb_1,@Level
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.sb_2,@Level
FROM tb a,@t_Level b
WHERE a.sb_1=b.sb_1
AND b.Level=@Level-1
END
RETURN
END
GOselect * from dbo.f_Pid(23)sb_1 Level
----------- -----------
23 1
24 2
25 2
32 3
26 4
33 5(6 行受影响)
如要查子结点,在函数中做相应操作即可