SQL 递归查询

create proc proc_tree (@node int)
as
begin
 declare @i int
 set @i = 0
 
 set nocount on
 drop table ##res
 create table ##res  ( node int, child int, weight int, op int)
 insert ##res select *,0 from tree where node = @node
 
 while exists(select * from ##res where op = 0 and child <> 0)
 begin
  set @i = @i + 1
  update ##res set op = @i where op = 0 and child <> 0
  insert ##res select a.*,0 from tree a,##res b where a.node = b.child and b.op = @i
 end
 
 select * from ##res
 set nocount off
end

 

 

copy别人的一个来看看：
--定义存储过程:
create procedure parts_explosion() //in @current char(25)
result(part_no char(25),structure_level integer,structure_qty decimal(15,6)
,children integer,siblings integer,open_children integer,same_level integer)

begin
declare @current char(25); --存放当前待查产品或零件
declare @level integer;    --存放产品层次
declare @structure_qty decimal(15,4);  --存放构成数量
select '最初产品编号' into @current;  --后台测试用
create table #stack(                  --中间结果存表
current_item char(25) null,     
part_level integer null,
part_qty decimal(15,4) null,
);
create table #parts(                  --最后返回结果存放表
part_no char(25) null,
structure_level integer null,
structure_qty decimal(15,6) null,
children integer null,                --存放如果有子级产品.则1.否0.
siblings integer null,                --存放是否邻居产品
open_children integer null,            --前台用
same_level integer null,              --前台用
);
/* SELECT @current='45860020A'*/
insert into #stack values(@current,1,1);  --初始中间表
select 1 into @level;                   
while @level>0 loop
if exists(select* from #stack where part_level=@level) then
select current_item,part_qty into @current,
@structure_qty from #stack
where part_level=@level;
if @level<>1 then
insert into #parts values(@current,@level,@structure_qty,1,1,1,1)
end if
;
delete from #stack
where part_level=@level and current_item=@current;
insert into #stack
select part_no,@level+1,part_qty
from product_stru
where product_no=@current;
if @@rowcount>0 then
select @level+1 into @level;
if @@rowcount=1 then
update #parts set same_level=0
where part_no=@current
end if
else
update #parts set children=0
where part_no=@current
end if
else
select @level-1 into @level
end if
end loop
;
select part_no,structure_level,structure_qty,children,siblings,open_children,same_level from #parts
end

 

--以下来自邹键的blog

--得到指定id的子id列表
create function f_getchildid(@id int)
returns @re table(id int)
as
begin
 insert into @re select id from tb where pid=@id
 while @@rowcount>0
  insert into @re select a.id
   from tb a inner join @re b on a.pid=b.id
   where a.id not in(select id from @re)
 return
end
go

--得到指定id的父id列表
create function f_getparentid(@id int)
returns @re table(id int)
as
begin
 declare @pid int
 select @pid=pid from tb where id=@id
 while @pid<>0
 begin
  insert into @re values(@pid)
  select @pid=pid from tb where id=@pid
 end
 return
end
go