求个SQL语句，一条实现我的需求

[存在666]

date_entry date_change name_clerk id_dept id_ndept id_post id_npost
2009/8/1 2012/2/1 A 20101 20103 20106 20105
2009/8/1 2011/3/31 A 20101 20101 20105 20106
2008/3/1 2012/2/1 B 20101 20103 20106 20105
2009/7/1 2011/6/3 C 40200 20906 20105 20105
2009/7/1 2011/5/7 C 20103 20102 20106 20105
2009/7/1 2011/3/31 C 20101 20103 20106 20106


想要每个用户的第一行的date_entry不变，第二行的date_entry 为上一行用户的date_change。以此类推！最后能到的结果是
date_entry date_change name_clerk id_dept id_ndept id_post id_npost
2009/8/1 2012/2/1 A 20101 20103 20106 20105
2012/2/1 2011/3/31 A 20101 20101 20105 20106
2008/3/1 2012/2/1 B 20101 20103 20106 20105
2011/5/7 2011/6/3 C 40200 20906 20105 20105
2011/3/31 2011/5/7 C 20103 20102 20106 20105
2009/7/1 2011/3/31 C 20101 20103 20106 20106

[jmx123456789]

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

[hackervip1988]

declare @table_b table
(date_entry datetime,date_change datetime,
name_clerk  varchar(24),id_dept int ,id_ndept int, 
 id_post int ,id_npost int)
 insert into @table_b
 Select '2009/8/1' ,	'2012/2/1', 'A' ,	20101,	20103,	20106,	20105
 union select
'2009/8/1',	'2011/3/31',	'A',	20101,	20101, 20105,	20106
union select
'2008/3/1',	'2012/2/1',	'B',	20101,	20103,	20106,	20105
union select 
'2009/7/1',	'2011/6/3',	'C',	40200,	20906 ,	20105 ,	20105
union select 
'2009/7/1' ,	'2011/5/7',  'C' ,	20103,	20102,	20106,	20105
union select
'2009/7/1',	'2011/3/31',	'C',	20101,	20103,	20106,	20106 ;
with ta as
(
  Select *, ROW_NUMBER() over(partition by name_clerk order by name_clerk) rid from @table_b
)

Select  tttt =
case when rid = 1 then date_entry else 
(select a.date_change from ta a where a.name_clerk = b.name_clerk and a.rid = b.rid - 1) end,
date_change ,name_clerk
From ta b