sqlserver中怎么实现递归查询?

[bluesun]

sqlserver中怎么实现递归查询?
有个部门结构表是树型的结构,

id upid caption
编号 上级编号 编号名称

我想选择了一个ID,就连它下面的ID都选择了

select id from table1 where id=3

select id from table1 where upid=3

[浩方软件HFWMS]

必须用循环才行

[浩方软件HFWMS]

参考:
ALTER proc DelNode
@id int
as
declare @FilterID int --左
declare @RightID int --右
declare @i int

delete from TableMenu where aid=@id
--if exists(select aid,topic_id from TableMenu where topic_id=@id)
select aid,topic_id from TableMenu where topic_id=@id
set @i=@@rowcount --节点扇出数
while @i>0
begin
select @FilterID=aid from TableMenu where topic_id=@id
--递归
exec DelNode @FilterID
--select @RightID=aid from TableMenu where topic_id=@id
--exec DelNode @RightID
set @i=@i-1
end

GO

[$扫地僧$]

create table A
(
id int,
upid int,
caption varchar(100)
)

insert A
select 1,0,'总经理办公室' union
select 2,1,'人事部' union
select 3,1,'业务部' union
select 4,3,'业务部(内)' union
select 5,3,'业务部(外)'



create proc Temp_proc(@ID int)
as
declare @T_levn int
declare @t1 table(id int,upid int,caption varchar(100),T_levn int)
set @T_levn=1
insert @t1 select A.*,@T_levn from A where A.id=@ID
while @@rowcount>0
begin
set @T_levn=@T_levn+1
insert @t1 select A.*,@T_levn from A where A.upid in (select id from @t1 where T_levn=@T_levn-1)
end
select * from @t1

exec Temp_proc 3

[$扫地僧$]

create table A
(
id int,
upid int,
caption varchar(100)
)

insert A
select 1,0,'总经理办公室' union
select 2,1,'人事部' union
select 3,1,'业务部' union
select 4,3,'业务部(内)' union
select 5,3,'业务部(外)'



create proc Temp_proc(@ID int)
as
declare @T_levn int
declare @t1 table(id int,upid int,caption varchar(100),T_levn int)
set @T_levn=1
insert @t1 select A.*,@T_levn from A where A.id=@ID
while @@rowcount>0
begin
set @T_levn=@T_levn+1
insert @t1 select A.*,@T_levn from A where A.upid in (select id from @t1 where T_levn=@T_levn-1)
end
select * from @t1

exec Temp_proc 3

[bluesun]

都是高手啊,用scmail81(freedom) 的方法实现了.

[mislrb]

学习

[wgd]

高手