did pdid dname cindex
19 23 业务1 1
20 24 业务2 1
21 25 业务3 1
22 26 业务4 1
23 28 经理1 1
24 29 经理2 1
25 30 经理3 1
上为表格，下为具体内容，当遇到一个人属于多个部门的时候，取出最高级的部门进行处理
--select uid from dbo.function_sxj(21)
--select * from ddept
---上下级权限表
create function function_sxj(@ddid int)
RETURNS @Table TABLE(uid int)
as
begin
declare @zzjg table(id int identity(1,1),did int,dname varchar(50),jb int)
declare @temp table(id int identity(1,1),did int)
declare @i int,@did int
insert into @temp(did)
select did from ddept where did=@ddid
set @i=1
--if exists(select top 1 1 from ddept a inner join @temp b on a.pdid=b.did)
--begin
while(isnull((select top 1 1 from ddept a inner join @temp b on a.pdid=b.did),0)<>0)
begin
insert into @zzjg(did,dname,jb)
select a.did,a.dname,@i from ddept a inner join @temp b on a.pdid=b.did
if exists(select top 1 1 from ddept a inner join @zzjg b on a.pdid=b.did where jb=@i)
begin
delete @temp
insert into @temp(did)
select did from @zzjg where jb=@i
set @i=@i+1
end
else
begin
delete @temp
end
end
select top 1 @did=b.did from dudd a inner join @zzjg b on a.did=b.did order by jb
insert @Table
select b.uid from @zzjg a inner join dudd b on a.did=b.did where jb>=(select jb from @zzjg where did=@did)
return
end
写了一个上下级关系，根据自己所在部门的编号，找到下级，不包括自己
原文：http://www.cnblogs.com/chlf/p/4232816.html