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

--创建单位信息表I_SYS_UnitInfo
ifexists(selectnamefromsysobjectswherename='I_SYS_UnitInfo')
droptableI_SYS_UnitInfo
go

createtableI_SYS_UnitInfo
(
UI_IDintidentity(1,1)notnullprimarykey,--单位ID
UI_ShortNamevarchar(20)notnull,--单位名称
P_UI_IDintnotnull,--所属单位ID
UI_optMarkintnotnulldefault1,--操作标记：0删除，1添加，2修改
UI_optIDintnotnulldefault0,
UI_optTimedatetimenotnulldefaultgetdate(),
)
go

--创建员工档案表I_HR_EmployeeArchives
ifexists(selectnamefromsysobjectswherename='I_HR_EmployeeArchives')
droptableI_HR_EmployeeArchives
go

createtableI_HR_EmployeeArchives
(
EA_IDintidentity(1,1)notnullprimarykey,--档案ID
EA_UI_IDintnotnull,--所属部门
EA_Namevarchar(50)notnull,--员工姓名
EA_optMarkintnotnulldefault1,--操作标记：0删除，1添加，2修改
EA_optIDintnotnulldefault0,
EA_Timedatetimenotnulldefaultgetdate(),
)
go

--查找属于编号为1的单位下的所有的员工
--定义要查询的单位
declare@UIDint
set@UID=1
as
--得到所有属于1的单位
DECLARE@tTABLE(UI_IDint,Levelint)
DECLARE@lint
SET@l=0
INSERT@tSELECTUI_ID,@lFROMI_SYS_UnitInfo
WHEREUI_ID=@UID
WHILE@@ROWCOUNT>0
BEGIN
SET@l=@l+1
INSERT@tSELECTA.UI_ID,@l
FROMI_SYS_UnitInfoA,@tB
WHEREA.P_UI_ID=B.UI_ID
ANDB.Level=@l-1
END

--统计
selectDISTINCTH.EA_ID,H.EA_Name,A.UI_ShortName,H.EA_UI_ID
from
(
SELECTAA.UI_ID,AA.UI_ShortName
from@tA,I_HR_EmployeeArchivesH,I_SYS_UnitInfoAA
whereA.UI_ID=H.EA_UI_ID
ANDA.UI_ID=AA.UI_IDandH.EA_optMark>0andAA.UI_optMark>0
)A
FULLJOIN(
SELECTAA.UI_ID,AA.UI_ShortName,H.EA_Name,H.EA_UI_ID,H.EA_ID
FROM@tA,I_HR_EmployeeArchivesH,I_SYS_UnitInfoAA
whereA.UI_ID=H.EA_UI_ID
ANDA.UI_ID=AA.UI_IDandH.EA_optMark>0andAA.UI_optMark>0
)H
ONA.UI_ID=H.EA_UI_ID
ORDERBYH.EA_UI_ID