多重关联表查询问题

[木村戒奶]

部门表：
id | name | belong(从属)
01 | 部门1| 00
02 | 部门2| 01
03 | 部门3| 00
员工表：
id | name | deptid
01 | 小红 | 01
02 | 小明 | 02
03 | 小张 | 03

怎样把部门1和从属部门1的部门2，所包含的员工都查找出来呢，谢谢！

[撸大湿]

CREATE FUNCTION findall (@table nvarchar(50),@myid int)
RETURNS @list table (id int) AS
BEGIN
declare @i int
insert into @list select @myid
set @i=0
DECLARE findid CURSOR FOR select id from department where father=@myid
Open findid
While @@FETCH_STATUS = 0
begin
FETCH next FROM findid into @i

if @@FETCH_STATUS = 0
insert into @list select @i union select id from dbo.findall(@i)
end
CLOSE findid
DEALLOCATE findid

return
END

[撸大湿]

然后
select * form 员工表 where deptid in (select id as deptid from select id from dbo.findall(1))

[撸大湿]

写错了
然后
select * form 员工表 where deptid in (select id as deptid from dbo.findall(1))

[dfwz2004]

学习

[撸大湿]

这个也改一下，去掉那个TABLE变量
CREATE FUNCTION findall （myid int)
RETURNS @list table (id int) AS
BEGIN
declare @i int
insert into @list select @myid
set @i=0
DECLARE findid CURSOR FOR select id from department where belong=@myid
Open findid
While @@FETCH_STATUS = 0
begin
FETCH next FROM findid into @i

if @@FETCH_STATUS = 0
insert into @list select @i union select id from dbo.findall(@i)
end
CLOSE findid
DEALLOCATE findid

return
END