日期:2014-05-17 浏览次数:20519 次
declare @tab table(date_entry datetime,date_change datetime,id varchar(10))
insert into @tab
select '2009/8/1','2012/2/1','A' union
select '2009/8/1','2011/3/31','A' union
select '2008/3/1','2012/2/1','B' union
select '2009/7/1','2011/6/3','C' union
select '2009/7/1','2011/5/7','C' union
select '2009/7/1','2011/3/31','C'
;with tab as(
select *,ROW_NUMBER() over(partition by id order by id) rid from @tab
)
select date_entry=case
when rid=1 then date_entry
else (select date_change from tab t where t.id=tab.id and t.rid=tab.rid-1) end
,date_change,id from tab
-------------------------------------------------
2009-08-01 00:00:00.000 2011-03-31 00:00:00.000 A
2011-03-31 00:00:00.000 2012-02-01 00:00:00.000 A
2008-03-01 00:00:00.000 2012-02-01 00:00:00.000 B
2009-07-01 00:00:00.000 2011-03-31 00:00:00.000 C
2011-03-31 00:00:00.000 2011-05-07 00:00:00.000 C
2011-05-07 00:00:00.000 2011-06-03 00:00:00.000 C
declare @table_b table
(date_entry datetime,date_change datetime,
name_clerk varchar(24),id_dept int ,id_ndept int,