SQL递归来取得树型目录下的数据
比如我有一个树型结构的表，全是Varchar型
ID ParentID Name
D0 -1 服装
D1 D0 上衣
D2 D0 裤子
D3 D1 中山装
D4 D3 圆领中山装
这样的一个树型结构出现了，比如我给点一个节点的ID，能查出这个节点下面所有的子节点，包含子节点下面的子节点
例如我给定一个 ID＝D1 就要取得 上衣 下面的所有信息，
比如上衣下面－>中山装->圆领中山装
D1 D0 上衣
D3 D1 中山装
D4 D3 圆领中山装
问题点数：100、回复次数：18Top
1 楼libin_ftsafe（子陌红尘：TS for Banking Card）回复于 2005-11-22 11:53:15 得分 80
--生成测试数据
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 BOMTop
2 楼libin_ftsafe（子陌红尘：TS for Banking Card）回复于 2005-11-22 11:57:55 得分 0
--生成测试数据
create table BOM(ID VARCHAR(10),ParentID VARCHAR(10),Name VARCHAR(1000))
INSERT INTO BOM select 'D0','-1','服装'
INSERT INTO BOM select 'D1','D0','上衣'
INSERT INTO BOM select 'D2','D0','裤子'
INSERT INTO BOM select 'D3','D1','中山装'
INSERT INTO BOM select 'D4','D3','圆领中山装'
go
--创建用户定义函数
create function f_getChild(@ID VARCHAR(10))
returns @t table(ID VARCHAR(10),ParentID VARCHAR(10),Name VARCHAR(100),Level INT)
as
begin
declare @i int,@ret varchar(8000)
set @i = 1
insert into @t select ID,ParentID,Name,@i from BOM where ID = @ID
while @@rowcount<>0
begin
set @i = @i + 1
insert into @t
select
a.ID,a.ParentID,a.Name,@i
from
BOM a,@t b
where
a.ParentID=b.ID and b.Level = @i-1
end
return
end
go
--执行查询
select ID,ParentID,Name from dbo.f_getChild('D1') order by Level
go
--输出结果
/*
ID ParentID Name
---- --------- ----------
D1 D0 上衣
D3 D1 中山装
D4 D3 圆领中山装
*/
--删除测试数据
drop function f_getChild
drop table BOMTop
3 楼samfeng_2003（凤翼天翔）回复于 2005-11-22 12:00:33 得分 20
/*----------去掉create前的括号---------*/
[create] table t
(id varchar(10),ParentID varchar(20),name varchar(20))
insert t values ('D0','-1','服装')
insert t values ('D1','D0','上衣')
insert t values ('D2','D0','裤子')
insert t values ('D3','D1','中山装')
insert t values ('D4','D3','圆领中山装')
go
create function f_gui(@col1 varchar(20))
returns @t table(id varchar(10),ParentID varchar(20),name varchar(20),level int)
as
begin
declare @i int
set @i=1
insert into @t select *,@i from t where id=@col1
while @@rowcount<>0
begin
set @i=@i+1
insert into @t
select a.*,@i from t a,@t b
where a.ParentID=b.id and b.level=@i-1
end
return
end
go
/*--------查询语句-------*/
select id,ParentID,name from dbo.f_gui('D1')
drop function f_gui
drop table t
/*-----------结果----------*/
id ParentID name
---------- -------------------- --------------------
D1 D0 上衣
D3 D1 中山装
D4 D3 圆领中山装
（所影响的行数为 3 行）Top
4 楼yangbo88（稳步前进）回复于 2005-11-22 12:01:38 得分 0
如果你做过ERP系统这个问题就简单了，用典型的递归方法。
要详细写出来太长了Top
5 楼samfeng_2003（凤翼天翔）回复于 2005-11-22 12:01:48 得分 0
哎！还是没有子陌老大快！^_^Top
6 楼suipianwuda（）回复于 2005-11-22 13:09:15 得分 0
能不能将 Function 改成一个存储过程,
如果的参数是 表名,ID字段名,ParentId字段名,Name字段名, 节点的值
当我 exec p_getChile 'myTree','ID','parentID','Name','D1'
后得到一个下面的数据
id ParentID name
---------- -------------------- --------------------
D1 D0 上衣
D3 D1 中山装
D4 D3 圆领中山装Top
7 楼suipianwuda（）回复于 2005-11-22 13:10:32 得分 0
能留QQ吗,以后多多向你们学习 ,我的QQ 59144413Top
8 楼libin_ftsafe（子陌红尘：TS for Banking Card）回复于 2005-11-22 13:39:22 得分 0
--生成测试数据
create table BOM(ID VARCHAR(10),ParentID VARCHAR(10),Name VARCHAR(1000))
INSERT INTO BOM select 'D0','-1','服装'
INSERT INTO BOM select 'D1','D0','上衣'
INSERT INTO BOM select 'D2','D0','裤子'
INSERT INTO BOM select 'D3','D1','中山装'
INSERT INTO BOM select 'D4','D3','圆领中山装'
go
--创建存储过程
create procedure sp_getChile
@TName nvarchar(40),
@IDName nvarchar(40),
@PIDName nvarchar(40),
@Name nvarchar(40),
@ID nvarchar(20)
as
begin
create table #T(ID VARCHAR(10),ParentID VARCHAR(10),Name VARCHAR(40),Level INT)
declare @s nvarchar(4000)
set @s = N'declare @i int set @i = 1'
set @s = @s + N'
insert into #t select '+@IDName+N','+@PIDName+N','+@Name+N',@i from '+@TName+N' where ID='''+@ID+N''''
set @s = @s + N'
while @@rowcount<>0
begin
set @i = @i + 1
insert into #t
select
a.'+@IDName+N',a.'+@PIDName+N',a.'+@Name+N',@i
from
'+@TName+N' a,#t b
where
a.'+@PIDName+N'=b.'+@IDName+N' and b.Level = @i-1
end'
exec(@s)
select ID,ParentID,Name from #T
end
go
--执行存储过程
exec sp_getChile 'BOM','ID','ParentID','Name','D1'
/*
id ParentID name
---------- -------------------- --------------------
D1 D0 上衣
D3 D1 中山装
D4 D3 圆领中山装
*/
--删除测试数据
drop procedure sp_getChile
drop table BOMTop
9 楼samfeng_2003（凤翼天翔）回复于 2005-11-22 13:53:48 得分 0
老大都写了！只好支持顶一下了！Top
10 楼suipianwuda（）回复于 2005-11-22 15:53:09 得分 0
请问如果要写成一个Function,该如何来写,我写不出来Top
11 楼libin_ftsafe（子陌红尘：TS for Banking Card）回复于 2005-11-22 15:59:05 得分 0
请问如果要写成一个Function,该如何来写,我写不出来
--------------------------------------------------------
函数内部不允许执行EXEC，除非执行扩展存储过程。Top
12 楼suipianwuda（）回复于 2005-11-22 16:03:48 得分 0
是的,我也是遇到这样的问题Top
13 楼suipianwuda（）回复于 2005-11-22 16:08:31 得分 0
除非执行扩展存储过程。??如何来做呀 ?
关键是我存储过程中查找出的数据,别的地方要用,比如
Select * from object where ID in (这里的数据就是存储过程中返回的数据集合的ID字段)Top
14 楼jhtchina（学习构架设计）回复于 2005-11-22 16:13:43 得分 0
Mark
学习。牛人太多Top
15 楼libin_ftsafe（子陌红尘：TS for Banking Card）回复于 2005-11-22 16:29:34 得分 0
利用之前的存储过程，可以这样变通一下：
create table #T(ID VARCHAR(10),ParentID VARCHAR(10),Name VARCHAR(40))
insert into #T exec sp_getChile 'BOM','ID','ParentID','Name','D1'
Select * from object where ID in(select ID from #T)Top
16 楼wk_1978（）回复于 2005-11-22 16:30:24 得分 0
好强啊，佩服Top
17 楼suipianwuda（）回复于 2005-11-22 16:53:15 得分 0
呵呵,强
能不能直接
Select * from Object where ID in (Select ID from 存储过程)????
Top
18 楼libin_ftsafe（子陌红尘：TS for Banking Card）回复于 2005-11-22 16:58:26 得分 0
能不能直接
Select * from Object where ID in (Select ID from 存储过程)????
-------------------------------------------------------------------------
Of course U can't.Top
相关问题
关键词
得分解答快速导航
相关链接
广告也精彩
反馈
