参考一下取字节点的函数:
--生成测试数据
create table BOM(ID INT,PID INT,MSG VARCHAR(1000))
insert into BOM select 1,0,NULL
insert into BOM select 2,1,NULL
insert into BOM select 3,1,NULL
insert into BOM select 4,2,NULL
insert into BOM select 5,3,NULL
insert into BOM select 6,5,NULL
insert into BOM select 7,6,NULL
go--创建用户定义函数
create function 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 ID,PID,@i from BOM where PID = @ID
while @@rowcount<>0
begin
set @i = @i + 1
insert into @t
select
a.ID,a.PID,@i
from
BOM a,@t b
where
a.PID=b.ID and b.Level = @i-1
end
return
end
go--执行查询
select ID from dbo.f_getChild(3)
go--输出结果
/*
5
6
7
*/--删除测试数据
drop function f_getChild
drop table BOM
--生成测试数据
create table BOM(ID INT,PID INT,MSG VARCHAR(1000))
insert into BOM select 1,0,NULL
insert into BOM select 2,1,NULL
insert into BOM select 3,1,NULL
insert into BOM select 4,2,NULL
insert into BOM select 5,3,NULL
insert into BOM select 6,5,NULL
insert into BOM select 7,6,NULL
go--创建用户定义函数
create function 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 ID,PID,@i from BOM where PID = @ID
while @@rowcount<>0
begin
set @i = @i + 1
insert into @t
select
a.ID,a.PID,@i
from
BOM a,@t b
where
a.PID=b.ID and b.Level = @i-1
end
return
end
go--执行查询
select ID from dbo.f_getChild(3)
go--输出结果
/*
5
6
7
*/--删除测试数据
drop function f_getChild
drop table BOM
create table BOM(ID INT,Name varchar(10),Sign int)
insert into BOM values(1,'a',0)
insert into BOM values(2,'b',1)
insert into BOM values(3,'c',2)
insert into BOM values(4,'d',2)
insert into BOM values(5,'e',3)
insert into BOM values(6,'f',0)
insert into BOM values(7,'g',6)
go--创建用户定义函数
create function f_getChild(@ID VARCHAR(10))
returns @t table(ID INT,Name VARCHAR(10),Sign INT,Level INT)
as
begin
declare @i int
set @i = 1
insert into @t
select ID,Name,Sign,@i from BOM where ID = @ID
while @@rowcount<>0
begin
set @i = @i + 1
insert into @t
select
a.ID,a.Name,a.Sign,@i
from
BOM a,@t b
where
a.Sign=b.ID and b.Level = @i-1
end
return
end
go--执行查询
select ID,Name,Sign from dbo.f_getChild(1)
go--输出结果
/*
ID Name Sign
----------- ---------- -----------
1 a 0
2 b 1
3 c 2
4 d 2
5 e 3
*/--删除测试数据
drop function f_getChild
drop table BOM
老大帮解释一下,这是存储过程 还是什么
老大帮解释一下,这是存储过程 还是什么----------------------------
--创建用户定义函数
CREATE TABLE t
(
id int,
name varchar(20),
sign int
)
insert into t
select 1,'a',0 UNION ALL
select 2,'b',1 UNION ALL
select 3,'c',2 UNION ALL
select 4,'d',2 UNION ALL
select 5,'e',3 UNION ALL
select 6,'f',0 UNION ALL
select 7,'g',6
go
with t_tree( id, name, [sign], lvl)
AS
(
SELECT id, name, [sign], 0
FROM t
WHERE id = 1
UNION ALL
SELECT A.id, A.name, A.[sign], B.lvl + 1
FROM t A, t_tree B
WHERE A.[sign] = B.id
)
SELECT * FROM t_treeDROP TABLE Tid name sign lvl
----------- -------------------- ----------- -----------
1 a 0 0
2 b 1 1
3 c 2 2
4 d 2 2
5 e 3 3(5 行受影响)