SQL递归算法
請大家幫我看一下：
有個表：dept_tb
  有2個字段：
dept_no1     dept_no2
11                   101
11                   102
11                   103
101                 10111
102                 10122
10111             11111
10122             12111
11111             111111

如果輸入：11，
結果為：101,102,103,10111,10122,12111,11111

------解决方案--------------------
create table dept_tb
(
dept_no1 int,
dept_no2 int
)

insert dept_tb select 11,101
insert dept_tb select 11,102
insert dept_tb select 11,103
insert dept_tb select 101,10111
insert dept_tb select 102,10122
insert dept_tb select 10111,11111
insert dept_tb select 10122,12111
insert dept_tb select 11111,111111


create Function T_Test(@dept_no int)
returns varchar(8000)
as
begin
declare @T_SQL varchar(8000)
set @T_SQL= ' '
declare @T table(dept_no1 int,dept_no2 int,lev int)
declare @lev int
set @lev=1
insert @T select dept_no1,dept_no2,@lev from dept_tb where dept_no1=@dept_no
while @@rowcount> 0
begin
set @lev=@lev+1
insert @T select D.dept_no1,D.dept_no2,@lev from dept_tb D,(select * from @T where lev=@lev-1) T where D.dept_no1=T.dept_no2
end
select @T_SQL=@T_SQL + cast(dept_no2 as varchar) + ', ' from @T where lev <> @lev
return left(@T_SQL,len(@T_SQL)-1)
end

select dbo.T_Test(11)
------解决方案--------------------
declare @dept_tb table(dept_no1 varchar(20) , dept_no2 varchar(20))
insert @dept_tb
select '11 ', '101 ' union all
select '11 ', '102 ' union all
select '11 ', '103 ' union all
select '101 ', '10111 ' union all
select '102 ', '10122 ' union all
select '10111 ', '11111 ' union all
select '10122 ', '12111 ' union all
select '11111 ', '111111 '

declare @id varchar(20)
declare @tmp table(id varchar(20))
set @id = '11 '

insert @tmp select dept_no2 from @dept_tb where dept_no1 = @id
while @@rowcount > 0
begin
insert @tmp select a.dept_no2 from @dept_tb as a inner join @tmp as b
on a.dept_no1 = b.id where a.dept_no2 not in(select id from @tmp)
end
----字符串连接
declare @str varchar(8000)
set @str = ' '
select @str = @str + ', ' + id from @tmp
set @str = case @str when ' ' then @str else stuff(@str,1,1, ' ') end
----返回子节点字符串
select @str

/*
结果：
101,102,103,10111,10122,12111,11111
*/
------解决方案--------------------

create table dept_tb
(
dept_no1 int,
dept_no2 int
)

insert dept_tb select 11,101
insert dept_tb select 11,102
insert dept_tb select 11,103
insert dept_tb select 101,10111
insert dept_tb select 102,10122
insert dept_tb select 10111,11111
insert dept_tb select 10122,12111
insert dept_tb select 11111,111111

GO
--递归查询所有的下级部门