帮帮忙，看看这个递归怎么写

[hphong]

表
ID CoustomID
1 2
2 3
58 12
5 6
12 23
3 9

其实其中有关系 CoustomID = 9时 ID=3,继续查CoustomID = 3时ID = 2 ……依此类推
如何写条语句，当输入CoustomID = 9时，要求得到串'1,2,3,9'
是否递归？

[vivianfdlpw]

递归限制32层以内,用循环比较好,参看:


--树形数据查询示例
--作者: 邹建

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 identity(1,1),[pid] int,name varchar(20))
insert [tb] select 0,'中国'
union all select 0,'美国'
union all select 0,'加拿大'
union all select 1,'北京'
union all select 1,'上海'
union all select 1,'江苏'
union all select 6,'苏州'
union all select 7,'常熟'
union all select 6,'南京'
union all select 6,'无锡'
union all select 2,'纽约'
union all select 2,'旧金山'
go

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_id]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_id]
GO

/*--树形数据处理

级别及排序字段

--邹建 2003-12(引用请保留此信息)--*/

/*--调用示例

--调用函数实现分级显示
select replicate('-',b.[level]*4)+a.name
from [tb] a,f_id()b
where a.[id]=b.[id]
order by b.sid
--*/
create function f_id()
returns @re table([id] int,[level] int,sid varchar(8000))
as
begin
declare @l int
set @l=0
insert @re select [id],@l,right(10000+[id],4)
from [tb] where [pid]=0
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.[id],@l,b.sid+right(10000+a.[id],4)
from [tb] a,@re b
where a.[pid]=b.[id] and b.[level]=@l-1
end
return
end
go

--调用函数实现分级显示
select replicate('-',b.[level]*4)+a.name
from [tb] a,f_id()b
where a.[id]=b.[id]
order by b.sid
go


--删除测试
drop table [tb]
drop function f_id
go

/*--结果
中国
----北京
----上海
----江苏
--------苏州
------------常熟
--------南京
--------无锡
美国
----纽约
----旧金山
加拿大

--*/

[Well]

/*
不知引用那位
*/
--建立測試環境
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tCore_Dept]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tCore_Dept]
GO

CREATE TABLE [dbo].[tCore_Dept] (
[DeptID] [varchar] (8) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL ,
[DeptName] [varchar] (80) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL ,
[SuperiorDeptID] [varchar] (8) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL
) ON [PRIMARY]
GO

/*
--相應字段對應數據
A A Root
AA AA A
AB AB A
ABA ABA AB
ABAA ABAA ABA
B B Root
BA BA B
C C Root
*/
*/
CREATE Procedure GetNextDeptTree
(@Child_node varchar(8))
--WITH ENCRYPTION
As
begin
SET NOCOUNT ON
DECLARE @Cnt int
Declare @i int
Declare @tmpnode varchar(8)

create table #stack (node varchar(8))
create table #stackTmp (node varchar(8))
create table #stackTmpXXX (node varchar(8))

insert into #stack
select DeptID from GetNextDept(@Child_node)

insert into #stackTmp
select DeptID from GetNextDept(@Child_node)

select @tmpnode = @Child_node
select @Cnt = count(*) from tCore_Dept
select @i = 0

loops:

select * from #stack
declare cur cursor for
select node from #stackTmp
open cur
fetch next from cur into @tmpnode
while @@FETCH_STATUS = 0
begin
if(ltrim(@tmpnode)='')
print 'NULL'
else
print @tmpnode
insert into #stack select DeptID from GetNextDept(@tmpnode)
insert into #stackTmpXXX select DeptID from GetNextDept(@tmpnode)
select @i = @i + 1
fetch next from cur into @tmpnode
end
CLOSE cur
DEALLOCATE cur

select @i = @i + 1

delete from #stackTmp
insert into #stackTmp select node from #stackTmpXXX
delete from #stackTmpXXX

if (@i >= @Cnt * 1.5 )
goto ends

goto loops

ends:
select node from #stack
end
GO

[hphong]

楼上的，这个表GetNextDept哪里去了？

[samfeng_2003]

[create] table t
(id int,CoustomID int)

insert t values (1,2)
insert t values (2,3)
insert t values (58,12)
insert t values (5,6)
insert t values (12,23)
insert t values (3,9)

go
create function f_xun(@id int)
returns varchar(200)
as
begin
declare @t table(id int,CoustomID int,level int)
declare @i int,@re varchar(200)
set @i=1

set @i=1
set @re=''

insert into @t
select id,CoustomID,@i from t where Coustomid=@id

while @@rowcount<>0
begin
set @i=@i+1

insert into @t
select a.id,a.CoustomID,@i
from t a,@t b
where a.Coustomid=b.id and b.level=@i-1
end
select @re=@re+','+cast(id as varchar) from @t order by id asc
return(stuff(@re+','+cast(@id as varchar),1,1,''))
end
go

select id,Coustomid=dbo.f_xun(Coustomid) from t where Coustomid=9

drop function f_xun
drop table t

id Coustomid
-------------- ----------------------------------------------
3 1,2,3,9

（所影响的行数为 1 行）

[vivianfdlpw]

create table A
(
ID int,
CoustomID int
)
insert A
select 1, 2 union
select 2, 3 union
select 58, 12 union
select 5, 6 union
select 12, 23 union
select 3, 9
go

--创建函数
create function f_str(@CoustomID int)
returns varchar(8000)
as
begin
declare @tb table(num int identity,CoustomID int)
insert @tb(CoustomID)
select distinct @CoustomID from A where exists(select 1 from A where CoustomID=@CoustomID)

while @@rowcount>0
begin
insert @tb(CoustomID)
select A.ID
from A
join @tb B on A.CoustomID=B.CoustomID
where not exists(select 1 from @tb where CoustomID=A.ID)
end

declare @str varchar(8000)
select @str=''
select @str=@str+','+convert(varchar,CoustomID) from @tb order by num desc
return stuff(@str,1,1,'')
end
go

--查询
select dbo.f_str(9)

--删除测试环境
drop function f_str
drop table A

--结果
/*
1,2,3,9

（所影响的行数为 1 行）
*/

[zzit0721]

create table a (id int,zid int) -----建立测试数据
insert into a select 1,2
insert into a select 2,3
insert into a select 58,12
insert into a select 5,6
insert into a select 12,23
insert into a select 3,9

create function xy(@x int,@y int)---建立函数
returns int
as
begin
declare
@a int
while exists(select 1 from a where zid=@y)
begin
select @a=id from a where zid=@y
if @a=@x
begin
return 1
end
else
set @y=@a
end
return 0
end



select * -----查询
from (select *
from (select id
from a) a ,(select zid from a) b
where dbo.xy(a.id,b.zid)=1 ) cc
where id=1


drop table a ---销毁测试数据
drop function xy

[hphong]

create table A
(
ID int,
CoustomID int
)
insert A
select 1, 2 union
select 2, 3 union
select 58, 12 union
select 5, 6 union
select 12, 23 union
select 3, 9
go

--创建函数
create function f_str(@CoustomID int)
returns varchar(8000)
as
begin
declare @tb table(num int identity,CoustomID int)
insert @tb(CoustomID)
select distinct @CoustomID from A where exists(select 1 from A where CoustomID=@CoustomID)

while @@rowcount>0
begin
insert @tb(CoustomID)
select A.ID
from A
join @tb B on A.CoustomID=B.CoustomID
where not exists(select 1 from @tb where CoustomID=A.ID)
end

declare @str varchar(8000)
select @str=''
select @str=@str+','+convert(varchar,CoustomID) from @tb order by num desc
return stuff(@str,1,1,'')
end
go

--查询
select dbo.f_str(9)

--删除测试环境
drop function f_str
drop table A