表1:per_employee 字段:emp_id emp_no emp_name 记录(3,"A1101","李白")表2:per_emp_transfer 字段:et_id emp_id 记录(1,3)表3:per_emp_transfer_detail 字段:et_id col_name o_data o_old_data 记录(1,"dept_id","aaa","bbb")
(2,"pos_id","aaa","bbb") 注:表3相当于表2的子表 在sql server2000上运行
要求查询出来的结果是:emp_no emp_name 变动前部门 变动前职位 变动后部门 变动后职位
A1101 李白 bbb bbb aaa aaa
(2,"pos_id","aaa","bbb") 注:表3相当于表2的子表 在sql server2000上运行
要求查询出来的结果是:emp_no emp_name 变动前部门 变动前职位 变动后部门 变动后职位
A1101 李白 bbb bbb aaa aaa
from 表1 A,表2 B,表2 C,表3 D,表3 E
where A.empid=B.emp_id
and C.etid=A.empid
and B.emp_id=D.et_id
and C.eet_id=E.et_id
表2:变动ID,员工ID
表3:变动ID,字段名,变动后,变动前要查员工部门和职位变动前后的数据 用一条记录显示
col_name="dept_id"就代表部门变动
col_name="pos_id" 就代表职位变动
go
create table per_employee(emp_id int, emp_no varchar(10), emp_name nvarchar(10))
insert per_employee select 3,'A1101',N'李白'
if object_id('per_emp_transfer')is not null drop table per_emp_transfer
go
create table per_emp_transfer (et_id int, emp_id int)
insert per_emp_transfer select 1,3
if object_id('per_emp_transfer_detail ')is not null drop table per_emp_transfer_detail
go
create table per_emp_transfer_detail (et_id int, [col_name] varchar(10), o_data varchar(10), o_old_data varchar(10))
insert per_emp_transfer_detail select 1,'dept_id','aaa','bbb'
insert per_emp_transfer_detail select 1,'pos_id','aaa','bbb' --改了條數據 應該是1 不是2吧
select a.emp_no,a.emp_name,c.o_old_data,d.o_old_data ,c.o_data ,d.o_data from per_employee a
inner join per_emp_transfer b on a.emp_id=b.emp_id
inner join per_emp_transfer_detail c on b.et_id=c.et_id and c.[col_name]='dept_id'
inner join per_emp_transfer_detail d on b.et_id=d.et_id and d.[col_name]='pos_id'
/*emp_no emp_name o_old_data o_old_data o_data o_data
---------- ---------- ---------- ---------- ---------- ----------
A1101 李白 bbb bbb aaa aaa(影響 1 個資料列)
*/
---------------------------------------
select T1.员工编号,T1.员工姓名,
变动前部门 = (select top 1 变动前 from T3 where col_name='dept_id' and T3.变动ID = T2.变动ID),
变动前职位 = (select top 1 变动前 from T3 where col_name='pos_id' and T3.变动ID = T2.变动ID),
变动后部门 = (select top 1 变动后 from T3 where col_name='dept_id' and T3.变动ID = T2.变动ID),
变动后职位 = (select top 1 变动后 from T3 where col_name='pos_id' and T3.变动ID = T2.变动ID)
from T2
inner join T1 on T2.员工ID = T1.员工ID
---------------------------------------
---------------------------------------
select T1.员工编号,T1.员工姓名,
变动前部门 = (select top 1 变动前 from T3 where col_name='dept_id' and T3.变动ID = T2.变动ID),
变动前职位 = (select top 1 变动前 from T3 where col_name='pos_id' and T3.变动ID = T2.变动ID),
变动后部门 = (select top 1 变动后 from T3 where col_name='dept_id' and T3.变动ID = T2.变动ID),
变动后职位 = (select top 1 变动后 from T3 where col_name='pos_id' and T3.变动ID = T2.变动ID)
from T2
inner join T1 on T2.员工ID = T1.员工ID
---------------------------------------
declare @per_employee table (emp_id int,emp_no nvarchar(10),emp_name nvarchar(10))
insert into @per_employee select 3,'A1101','李白'
declare @Per_emp_transfer table (et_id int,emp_id int)
insert into @Per_emp_transfer select 1,3
declare @per_emp_transfer_detail table (et_id int,[col_name] nvarchar(10),o_data nvarchar(10),o_old_data nvarchar(10) )
insert into @per_emp_transfer_detail
select 1,'dept_id','aaa','bbb'
union all
select 2,'pos_id','aaa','bbb'
select a.emp_no,a.emp_name,c.o_old_data as 变动前部门,
c.o_old_data as 变动前职位,c.o_data as 变动后部门,
c.o_data as 变动后职位
from @per_employee a
join @Per_emp_transfer b on a.emp_id= b.emp_id
join @per_emp_transfer_detail c on b.et_id=c.et_id
emp_no emp_name 变动前部门 变动前职位 变动后部门 变动后职位
---------- ---------- ---------- ---------- ---------- ----------
A1101 李白 bbb bbb aaa aaa(1 行受影响)
m.emp_name ,
变动前部门 = (select o_old_data from per_emp_transfer_detail t1, per_emp_transfer t2 where t1.col_name = 'dept_id' and t1.et_id = t2.et_id and t2.emp_id = m.emp_id),
变动前职位 = (select o_old_data from per_emp_transfer_detail t1, per_emp_transfer t2 where t1.col_name = 'pos_id' and t1.et_id = t2.et_id and t2.emp_id = m.emp_id),
变动后部门 = (select o_data from per_emp_transfer_detail t1, per_emp_transfer t2 where t1.col_name = 'dept_id' and t1.et_id = t2.et_id and t2.emp_id = m.emp_id)
变动后职位 = (select o_data from per_emp_transfer_detail t1, per_emp_transfer t2 where t1.col_name = 'pos_id' and t1.et_id = t2.et_id and t2.emp_id = m.emp_id),
from per_employee m
select m.emp_no ,
m.emp_name ,
变动前部门 = (select o_old_data from per_emp_transfer_detail t1, per_emp_transfer t2 where t1.col_name = 'dept_id' and t1.et_id = t2.et_id and t2.emp_id = m.emp_id),
变动前职位 = (select o_old_data from per_emp_transfer_detail t1, per_emp_transfer t2 where t1.col_name = 'pos_id' and t1.et_id = t2.et_id and t2.emp_id = m.emp_id),
变动后部门 = (select o_data from per_emp_transfer_detail t1, per_emp_transfer t2 where t1.col_name = 'dept_id' and t1.et_id = t2.et_id and t2.emp_id = m.emp_id)
变动后职位 = (select o_data from per_emp_transfer_detail t1, per_emp_transfer t2 where t1.col_name = 'pos_id' and t1.et_id = t2.et_id and t2.emp_id = m.emp_id),
from per_employee m--如果有多次变更
select m.emp_no ,
m.emp_name ,
变动前部门 = (select top 1 o_old_data from per_emp_transfer_detail t1, per_emp_transfer t2 where t1.col_name = 'dept_id' and t1.et_id = t2.et_id and t2.emp_id = m.emp_id),
变动前职位 = (select top 1 o_old_data from per_emp_transfer_detail t1, per_emp_transfer t2 where t1.col_name = 'pos_id' and t1.et_id = t2.et_id and t2.emp_id = m.emp_id),
变动后部门 = (select top 1 o_data from per_emp_transfer_detail t1, per_emp_transfer t2 where t1.col_name = 'dept_id' and t1.et_id = t2.et_id and t2.emp_id = m.emp_id)
变动后职位 = (select top 1 o_data from per_emp_transfer_detail t1, per_emp_transfer t2 where t1.col_name = 'pos_id' and t1.et_id = t2.et_id and t2.emp_id = m.emp_id),
from per_employee m