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
--递归查询所有的下级部门

CREATE FUNCTION GetDepartList(@Depart int)
RETURNS nvarchar(2000)
AS
BEGIN
DECLARE @Return nvarchar(2000)
IF EXISTS(SELECT 1 FROM dept_tb WHERE dept_no1=@Depart)
BEGIN
SELECT @Return=ISNULL(@Return, ' ')+ ', '+CAST(dept_no2 AS nvarchar(50)) FROM dept_tb WHERE dept_no1=@Depart
IF EXISTS(SELECT 1 FROM dept_tb AS A WHERE EXISTS(SELECT 1 FROM dept_tb AS B WHERE B.dept_no1=@Depart AND A.dept_no1=B.dept_no2))
SELECT @Return=ISNULL(@Return, ' ')+ ', '+dbo.GetDepartList(dept_no2) FROM dept_tb WHERE dept_no1=@Depart
END
IF LEFT(@Return,1)= ', ' SET @Return=RIGHT(@Return,LEN(@Return)-1)
IF RIGHT(@Return,1)= ', ' SET @Return=LEFT(@Return,LEN(@Return)-1)
RETURN ISNULL(@Return, ' ')
END

GO

SELECT dbo.GetDepartList(11)

GO
DROP TABLE dept_tb
DROP FUNCTION GetDepartList
/*
预计果：101,102,103,10111,10122,12111,11111

实际结果：101,102,103,10111,11111,111111,10122,12111

--忽悠，不知道楼主为什么得那数。
*/