34,588
社区成员
发帖
与我相关
我的任务
分享
create table tb(id varchar(10) , PID varchar(10))
insert into tb values('A' , null)
insert into tb values('A1' , 'A')
insert into tb values('A11', 'A1')
go
--查询指定节点及其所有子节点的函数
CREATE FUNCTION f_Cid(@ID varchar(10)) RETURNS @t_Level TABLE(ID varchar(10),Level int)
AS
BEGIN
DECLARE @Level int
SET @Level=1
INSERT @t_Level SELECT @ID,@Level
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,@Level
FROM tb a,@t_Level b
WHERE a.PID=b.ID
AND b.Level=@Level-1
END
RETURN
END
GO
--调用函数查询id = 1及其所有子节点
SELECT a.* FROM tb a,f_Cid('A') b WHERE a.ID=b.ID
/*
id PID
---------- ----------
A NULL
A1 A
A11 A1
(所影响的行数为 3 行)
*/
SELECT a.* FROM tb a,f_Cid('A1') b WHERE a.ID=b.ID
/*
id PID
---------- ----------
A1 A
A11 A1
(所影响的行数为 2 行)
*/
drop table tb
drop function dbo.f_cid
--生成测试数据
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
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
--输出结果
/*
ID
----
5
6
7
*/
--删除测试数据
drop function f_getChild
drop table BOM