請大家幫我看一下：
有個表：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 [email protected]_no
while @@rowcount> 0
begin
set @[email protected]+1
insert @T select D.dept_no1,D.dept_no2,@lev from dept_tb D,(select * from @T where [email protected]) T where D.dept_no1=T.dept_no2
end
select @[email protected]_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
--递归查询所有的下级部门