2005 可以直接查询(如下所示), 2000得写一批语句DECLARE @tb TABLE( code int, name varchar(10), icode int) INSERT @tb SELECT 0, 'A', -1 UNION ALL SELECT 1, 'b', 0 UNION ALL SELECT 2, 'c', 0 UNION ALL SELECT 3, 'd', 0 UNION ALL SELECT 4, 'E', 1 UNION ALL SELECT 5, 'F', 1 UNION ALL SELECT 6, 'G', 2 UNION ALL SELECT 7, 'H', 2 UNION ALL SELECT 8, 'I', 4 UNION ALL SELECT 9, 'J', 6-- 查询 DECLARE @code int SET @code = 1;WITH TR AS( SELECT * FROM @tb WHERE code = @code UNION ALL SELECT A.* FROM @tb A, TR WHERE A.icode = TR.code ) SELECT * FROM TR
结果:1 b 0 4 E 1 5 F 1 8 I 4
--生成测试数据 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
code int,
name varchar(10),
icode int)
INSERT @tb
SELECT 0, 'A', -1 UNION ALL
SELECT 1, 'b', 0 UNION ALL
SELECT 2, 'c', 0 UNION ALL
SELECT 3, 'd', 0 UNION ALL
SELECT 4, 'E', 1 UNION ALL
SELECT 5, 'F', 1 UNION ALL
SELECT 6, 'G', 2 UNION ALL
SELECT 7, 'H', 2 UNION ALL
SELECT 8, 'I', 4 UNION ALL
SELECT 9, 'J', 6-- 查询
DECLARE
@code int
SET
@code = 1;WITH
TR AS(
SELECT * FROM @tb
WHERE code = @code
UNION ALL
SELECT A.*
FROM @tb A, TR
WHERE A.icode = TR.code
)
SELECT * FROM TR
4 E 1
5 F 1
8 I 4
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