部门ID、部门名称、结束时间 主键: 部门ID + 部门名称+ 结束时间下面查询是查"指定时间"时的部门信息:declare @ smalldatetime set @='2002-9-20' select * from #Departments T where datediff(day,结束时间,@)<=0 and datediff(day,结束时间 , (select min(结束时间) from #Departments where T.部门ID = 部门ID and datediff(day,结束时间,@)<=0))=0
select * into #Departments from (select '01' 部门ID,'hr' 部门名称,cast('2002-12-1' as datetime) 结束时间 union select '01','hr1',cast('2002-10-1' as datetime) union select '01','hr2',cast('2002-9-1' as datetime)) T
放在两个表中,对于一个查询,如何知道查那个表?除非首先查历史纪录,如果没有再查当前表,不如放在一个表中(个人观点,请批评)
原来的部门id不能删除
主键: 部门ID + 部门名称+ 结束时间下面查询是查"指定时间"时的部门信息:declare @ smalldatetime
set @='2002-9-20'
select *
from #Departments T
where datediff(day,结束时间,@)<=0
and datediff(day,结束时间 , (select min(结束时间)
from #Departments
where T.部门ID = 部门ID
and datediff(day,结束时间,@)<=0))=0
from
(select '01' 部门ID,'hr' 部门名称,cast('2002-12-1' as datetime) 结束时间
union
select '01','hr1',cast('2002-10-1' as datetime)
union
select '01','hr2',cast('2002-9-1' as datetime)) T