create table #temp ( 工号 varchar(50), 姓名 varchar(50), 部门 varchar(50), 开始时间 datetime, 结束时间 datetime )insert into #temp select '0001001','张三','销售部','2010/1/1','2011/1/1' union all select '0001001','张三','销售部','2011/1/1','2012/1/1' union all select '0001001','张三','办公室','2012/1/1','2013/1/1' union all select '0001001','张三','销售部','2013/1/1',null union all select '0001002','李四','生产部','2010/1/1','2011/1/1' union all select '0001002','李四','生产部','2011/1/1','2012/1/1' union all select '0001002','李四','办公室','2012/1/1','2013/1/1' union all select '0001002','李四','办公室','2013/1/1',null select 工号,姓名,部门,min(开始时间) as 开始时间,max(结束时间) as 结束时间 from #tempgroup by 工号,姓名,部门
select 工号,姓名,部门,min(开始时间) as 开始时间,max(结束时间) as 结束时间 from tb group by 工号,姓名,部门
with cte as ( select '0001001'as id,'张三' as name, '销售部' as deptname, '2010/01/01' as begintime,'2011/01/01' as endtime union all select'0001001','张三','销售部','2011/01/01','2012/01/01' union all select'0001001','张三','办公室','2012/01/01','2013/01/01' union all select'0001001','张三','销售部','2013/01/01', null union all select'0001002','李四','生产部','2010/01/01','2011/01/01' union all select'0001002','李四','生产部','2011/01/01','2012/01/01' union all select'0001002','李四','办公室','2012/01/01','2013/01/01' union all select'0001002','李四','办公室','2013/01/01',null ), cte1 as (select id,name,deptname,begintime,ISNULL(endtime,'9999-12-31') as endtime,ROW_NUMBER()over(PARTITION by id order by begintime)as n from cte), cte2 as (select ID,name,deptname,begintime,endtime,n,1 as groupid from cte1 where N=1 union all select a.ID,a.name,a.deptname,a.begintime,a.endtime,a.n,case when a.deptname=b.deptname then b.groupid else b.groupid+1 end as groupid from cte1 as a join cte2 as b on a.n=b.n+1 and a.id=b.id) select id,name,deptname,MIN(begintime)as begintime,MAX(endtime) as endtime from cte2 group by groupid,id,name,deptname order by id,begintime--结果 id name deptname begintime endtime ------- ---- -------- ---------- ---------- 0001001 张三 销售部 2010/01/01 2012/01/01 0001001 张三 办公室 2012/01/01 2013/01/01 0001001 张三 销售部 2013/01/01 9999-12-31/*9999-12-31表示至今*/ 0001002 李四 生产部 2010/01/01 2012/01/01 0001002 李四 办公室 2012/01/01 9999-12-31
create table #temp
(
工号 varchar(50),
姓名 varchar(50),
部门 varchar(50),
开始时间 datetime,
结束时间 datetime
)insert into #temp
select '0001001','张三','销售部','2010/1/1','2011/1/1' union all
select '0001001','张三','销售部','2011/1/1','2012/1/1' union all
select '0001001','张三','办公室','2012/1/1','2013/1/1' union all
select '0001001','张三','销售部','2013/1/1',null union all
select '0001002','李四','生产部','2010/1/1','2011/1/1' union all
select '0001002','李四','生产部','2011/1/1','2012/1/1' union all
select '0001002','李四','办公室','2012/1/1','2013/1/1' union all
select '0001002','李四','办公室','2013/1/1',null
select 工号,姓名,部门,min(开始时间) as 开始时间,max(结束时间) as 结束时间 from #tempgroup by 工号,姓名,部门
group by 工号,姓名,部门
(
select '0001001'as id,'张三' as name, '销售部' as deptname, '2010/01/01' as begintime,'2011/01/01' as endtime union all
select'0001001','张三','销售部','2011/01/01','2012/01/01' union all
select'0001001','张三','办公室','2012/01/01','2013/01/01' union all
select'0001001','张三','销售部','2013/01/01', null union all
select'0001002','李四','生产部','2010/01/01','2011/01/01' union all
select'0001002','李四','生产部','2011/01/01','2012/01/01' union all
select'0001002','李四','办公室','2012/01/01','2013/01/01' union all
select'0001002','李四','办公室','2013/01/01',null ),
cte1 as
(select id,name,deptname,begintime,ISNULL(endtime,'9999-12-31') as endtime,ROW_NUMBER()over(PARTITION by id order by begintime)as n from cte),
cte2 as
(select ID,name,deptname,begintime,endtime,n,1 as groupid from cte1 where N=1
union all
select a.ID,a.name,a.deptname,a.begintime,a.endtime,a.n,case when a.deptname=b.deptname then b.groupid else b.groupid+1 end as groupid
from cte1 as a join cte2 as b on a.n=b.n+1 and a.id=b.id)
select id,name,deptname,MIN(begintime)as begintime,MAX(endtime) as endtime from cte2
group by groupid,id,name,deptname
order by id,begintime--结果
id name deptname begintime endtime
------- ---- -------- ---------- ----------
0001001 张三 销售部 2010/01/01 2012/01/01
0001001 张三 办公室 2012/01/01 2013/01/01
0001001 张三 销售部 2013/01/01 9999-12-31/*9999-12-31表示至今*/
0001002 李四 生产部 2010/01/01 2012/01/01
0001002 李四 办公室 2012/01/01 9999-12-31