2叉树

--得到部门和员工姓名
create function dbo.GetTreeInfo
(
@Employe_Number as varchar(10)                                          -----员工编号
)
returns @TreeInformation table
(
Employe_Number varchar(10) not null,                                    ----员工编号
Employe_Name varchar(10) not null,                                       ----员工姓名   
Employe_Manager_Number varchar(10)not null,                     ----上级员工号
Dept_Name varchar(10)not null,                                              ----部门编号
ELevel int not null
)as
begin
declare @level as int
select @level=0
insert into @TreeInformation
select A.Employe_Number,A.Employe_Name,A.Employe_Manager_Number,C.Dept_Name,@level from Employe as A
right join Employe_Dept as B on A.Employe_Number=B.Employe_Number
right join Dept as C on B.Dept_Number=C.Dept_Number where  A.Employe_Number=@Employe_Number
while @@rowcount>0
begin
set @level=@level+1
insert into @TreeInformation
select D.Employe_Number,D.Employe_Name,D.Employe_Manager_Number,E.Dept_Name,@level from
(select A.Employe_Number,A.Employe_Name,A.Employe_Manager_Number,C.Dept_Name from Employe as A
right join Employe_Dept as B on A.Employe_Number=B.Employe_Number
right join Dept as C on B.Dept_Number=C.Dept_Number) as D
join @TreeInformation as E
on D.Employe_Manager_Number=E.Employe_Number and  E.ELevel=@level-1
end
return
end




--遍历请假审核人
create function dbo.GetSubtreeInfo
(
@Employe_Number as varchar(10)
)
returns @treeinfo table
(
Employe_Number varchar(10) not null,
Employe_Name varchar(10) not null,
Employe_Manager_Number varchar(10)not null,
ELevel int not null 
)as
begin
declare @level as int
select @level=0
insert into @treeinfo
select Employe_Number,Employe_Name,Employe_Manager_Number,@level
from Employe where Employe_Number=@Employe_Number
while @@rowcount >0
begin
set @level=@level+1
insert into @treeinfo
select A.Employe_Number,A.Employe_Name,A.Employe_Manager_Number,@level
from Employe as A join @treeinfo as B
on A.Employe_Number = B.Employe_Manager_Number and B.ELevel=@level-1
end
return
end