--创建单位信息表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_UnitInfo A, @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_EmployeeArchives H,I_SYS_UnitInfo AA
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_EmployeeArchives H,I_SYS_UnitInfo AA
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
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_UnitInfo A, @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_EmployeeArchives H,I_SYS_UnitInfo AA
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_EmployeeArchives H,I_SYS_UnitInfo AA
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