for example: --生成测试数据 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 count(ID) from dbo.f_getChild(3) go--输出结果 /* 5 6 7 */--删除测试数据 drop function f_getChild drop table BOM
--谢谢子陌 --整理了一下 --环境 --win 2k p --sql server 2000-- 创建测试数据 if exists (select * from dbo.sysobjects where id = object_id(N'[tb]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [tb] GO-- 示例数据 create table [tb]([id] int PRIMARY KEY,[pid] int,name nvarchar(20)) INSERT [tb] SELECT 1,0,N'中国' UNION ALL SELECT 2,0,N'美国' UNION ALL SELECT 3,0,N'加拿大' UNION ALL SELECT 4,1,N'北京' UNION ALL SELECT 5,1,N'上海' UNION ALL SELECT 6,1,N'江苏' UNION ALL SELECT 7,6,N'苏州' UNION ALL SELECT 8,7,N'常熟' UNION ALL SELECT 9,6,N'南京' UNION ALL SELECT 10,6,N'无锡' UNION ALL SELECT 11,2,N'纽约' UNION ALL SELECT 12,2,N'旧金山' UNION ALL SELECT 13,8,N'新城区' GO -- 查询指定id的所有父 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_getParent]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].[f_getParent] GOCREATE FUNCTION [f_getParent](@id int) RETURNS @re TABLE(id int,pid int,level int) AS begin declare @level int set @level = 1 declare @pid int select @pid = pid from tb where id = @id insert @re select id,pid,@level from tb where id = @pid while @@rowcount > 0 begin set @level = @level + 1 select @pid = pid from tb where id = @pid insert @re select id,pid,@level from tb where id = @pid end return end GO --调用(查询所有的父) SELECT * from f_getParent(8) GO --查询指定ID所有子 --创建用户定义函数 --下函数来自CSDN: libin_ftsafe(子陌红尘) if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_getChild]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].[f_getChild] GOcreate function f_getChild(@ID VARCHAR(10)) returns @t table(ID int,PID int,Level INT) as begin declare @i int,@ret varchar(8000) set @i = 1 insert into @t select ID,PID,@i from tb where PID = @ID while @@rowcount<>0 --递归边界条件 begin set @i = @i + 1 insert into @t select a.ID,a.PID,@i from tb a,@t b where a.PID=b.ID and b.Level = @i-1 --level的作用体现 end return end go --调用(查询所有的子) select * from f_getChild(6) go
--生成测试数据
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 count(ID) from dbo.f_getChild(3)
go--输出结果
/*
5
6
7
*/--删除测试数据
drop function f_getChild
drop table BOM
--整理了一下
--环境
--win 2k p
--sql server 2000-- 创建测试数据
if exists (select * from dbo.sysobjects where id = object_id(N'[tb]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [tb]
GO-- 示例数据
create table [tb]([id] int PRIMARY KEY,[pid] int,name nvarchar(20))
INSERT [tb] SELECT 1,0,N'中国'
UNION ALL SELECT 2,0,N'美国'
UNION ALL SELECT 3,0,N'加拿大'
UNION ALL SELECT 4,1,N'北京'
UNION ALL SELECT 5,1,N'上海'
UNION ALL SELECT 6,1,N'江苏'
UNION ALL SELECT 7,6,N'苏州'
UNION ALL SELECT 8,7,N'常熟'
UNION ALL SELECT 9,6,N'南京'
UNION ALL SELECT 10,6,N'无锡'
UNION ALL SELECT 11,2,N'纽约'
UNION ALL SELECT 12,2,N'旧金山'
UNION ALL SELECT 13,8,N'新城区'
GO
-- 查询指定id的所有父
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_getParent]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_getParent]
GOCREATE FUNCTION [f_getParent](@id int)
RETURNS @re TABLE(id int,pid int,level int)
AS
begin
declare @level int
set @level = 1
declare @pid int
select @pid = pid from tb where id = @id
insert @re
select id,pid,@level from tb where id = @pid
while @@rowcount > 0
begin
set @level = @level + 1
select @pid = pid from tb where id = @pid
insert @re
select id,pid,@level from tb where id = @pid
end
return
end
GO
--调用(查询所有的父)
SELECT * from f_getParent(8)
GO --查询指定ID所有子
--创建用户定义函数
--下函数来自CSDN: libin_ftsafe(子陌红尘)
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_getChild]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_getChild]
GOcreate function f_getChild(@ID VARCHAR(10))
returns @t table(ID int,PID int,Level INT)
as
begin
declare @i int,@ret varchar(8000)
set @i = 1
insert into @t select ID,PID,@i from tb where PID = @ID
while @@rowcount<>0 --递归边界条件
begin
set @i = @i + 1
insert into @t
select
a.ID,a.PID,@i
from
tb a,@t b
where
a.PID=b.ID and b.Level = @i-1 --level的作用体现
end
return
end
go
--调用(查询所有的子)
select * from f_getChild(6)
go