create table t1(dptid int, prentdptid int)
insert into t1
select 1,0 union all
select 2,1 union all
select 3, 1 union all
select 4, 3 union all
select 5, 4 union all
select 6, 0
create table t2(userid int, dptid int)
insert into t2
select 1, 1 union all
select 2, 5 union all
select 3, 2 union all
select 4, 3
go
/*要求通过一个部门ID找出所有属于这个部门以及子部门的所有用户
注:部门级数不 */
create proc p1(@dptid int)
as
declare @i int
declare @tb table (dptid int,parentid int,level int)
set @i=0
insert into @tb
select dptid,prentdptid,@i from t1 where dptid=@dptid
while @@rowcount>0
begin
set @i=@i+1
insert into @tb
select a.dptid,a.prentdptid,@i
from t1 a,@tb b
where a.prentdptid=b.dptid and b.level=@i-1
end
select b.userid
from @tb a,t2 b
where a.dptid=b.dptid
go
create procedure xxxx
@dptid int
as
select * from 人员表
where dptid in
(
select dptid from 部门表 where dptid = 1
union
select dptid from 部门表 where prentdptid = 1)