关于树形结构的表与另外一个表的连接查询

-- 创建单位信息表I_SYS_UnitInfo
if exists ( select name from sysobjects where name = ' I_SYS_UnitInfo ' )
drop table I_SYS_UnitInfo
go

create table I_SYS_UnitInfo
(
UI_ID int identity ( 1 , 1 ) not null primary key , -- 单位ID
UI_ShortName varchar ( 20 ) not null , -- 单位名称
P_UI_ID int not null , -- 所属单位ID
UI_optMark int not null default 1 , -- 操作标记：0删除，1添加，2修改
UI_optID int not null default 0 ,
UI_optTime datetime not null default getdate (),
)
go

-- 创建员工档案表I_HR_EmployeeArchives
if exists ( select name from sysobjects where name = ' I_HR_EmployeeArchives ' )
drop table I_HR_EmployeeArchives
go

create table I_HR_EmployeeArchives
(
EA_ID int identity ( 1 , 1 ) not null primary key , -- 档案ID
EA_UI_ID int not null , -- 所属部门
EA_Name varchar ( 50 ) not null , -- 员工姓名
EA_optMark int not null default 1 , -- 操作标记：0删除，1添加，2修改
EA_optID int not null default 0 ,
EA_Time datetime not null default getdate (),
)
go

--查找属于编号为1的单位下的所有的员工
-- 定义要查询的单位
declare @UID int
set @UID = 1
as
-- 得到所有属于1的单位
DECLARE @t TABLE (UI_ID int , Level int )
DECLARE @l int
SET @l = 0
INSERT @t SELECT UI_ID, @l FROM I_SYS_UnitInfo
WHERE UI_ID = @UID
WHILE @@ROWCOUNT > 0
BEGIN
SET @l = @l + 1
INSERT @t SELECT A.UI_ID, @l
FROM I_SYS_UnitInfoA, @t B
WHERE A.P_UI_ID = B.UI_ID
AND B. Level = @l - 1
END

-- 统计
select DISTINCT H.EA_ID,H.EA_Name,A.UI_ShortName,H.EA_UI_ID
from
(
SELECT AA.UI_ID,AA.UI_ShortName
from @t A,I_HR_EmployeeArchivesH,I_SYS_UnitInfoAA
where A.UI_ID = H.EA_UI_ID
AND A.UI_ID = AA.UI_ID and H.EA_optMark > 0 and AA.UI_optMark > 0
)A
FULL JOIN (
SELECT AA.UI_ID,AA.UI_ShortName,H.EA_Name,H.EA_UI_ID,H.EA_ID
FROM @t A,I_HR_EmployeeArchivesH,I_SYS_UnitInfoAA
where A.UI_ID = H.EA_UI_ID
AND A.UI_ID = AA.UI_ID and H.EA_optMark > 0 and AA.UI_optMark > 0
)H
ON A.UI_ID = H.EA_UI_ID
ORDER BY H.EA_UI_ID