SQL存储过程递归问题。

Question

SQL Server 2000下关于查找子节点的函数示例：
--生成测试数据
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

Answer 1

SQL2005下查找子节点，删除子节点示例：
create table Tree(nId smallint, NodeName nvarchar(16), pId smallint)
insert Tree select 1, '中国', NULL
union all select 2, '北京', 1
union all select 3, '上海', 1
union all select 4, '湖北', 1
union all select 5, '湖南', 1
union all select 6, '武汉', 4
union all select 7, '孝感', 4
union all select 8, '长沙', 5
union all select 9, '岳阳', 5
union all select 10, '海淀区', 2
union all select 11, '朝阳区', 2
union all select 12, '上地', 10
union all select 13, '西三旗', 10go
create procedure sp_GetNoedsByRootID
@nId smallint
as
begin
declare @sql nvarchar(max)
set @sql='
with NewTree(nId, NodeName, pId) as
(
select * from Tree where nId=@nId
union all
select T.* from Tree T join NewTree NT on T.pId=NT.nId
)
select NT.nId, NT.NodeName, PT.NodeName as [ParentName]
from NewTree NT join Tree PT on NT.pId=PT.nId
'
exec sp_executesql @sql, N'@nId smallint', @nId
end
gocreate procedure sp_DelNoedsByRootID
@nId smallint
as
begin
declare @sql nvarchar(max)
set @sql='
with NewTree(nId, NodeName, pId) as
(
select * from Tree where nId=@nId
union all
select T.* from Tree T join NewTree NT on T.pId=NT.nId
)
delete Tree 
from Tree T join NewTree NT on T.nId=NT.nId
'
exec sp_executesql @sql, N'@nId smallint', @nId
end
go--查找节点及子节点
exec sp_GetNoedsByRootID 2--删除节点及子节点
exec sp_DelNoedsByRootID 2
select * from Treedrop table Tree
drop procedure sp_GetNoedsByRootID, sp_DelNoedsByRootID

Answer 2

cte;with xx as 
(
select...--起始条件union allselect   --递归条件
where ...
）
select * from xx

Answer 3

BOM 哈～～估计这位大哥对制造业系统比较熟悉～～

Answer 4

如果是ORACLE可以用connect by ...start with ..语句。
此处 start with id='输入节点号' connect by 父节点 prior= 子节点

Answer 5

高手们帮个忙~~
我对SQL语句不熟。。