update a set Etime=(select top 1 Stime from tb where name=a.name order by Stime>a.Stime order by Stime) from tb a
declare @table table (name varchar(4),Stime varchar(10),Etime varchar(10),Jianli varchar(12)) insert into @table select '王一','2006-01-01','2007-08-06','aaaaaaaaaaaa' union all select '王一','2007-08-06',null,'bbbbbbbbbbb' union all select '王一','2011-05-06',null,'cccccccccccc'select t.name,t.stime, case when t.rid<3 then m.Stime else t.etime end as etime,t.jianli from (select *,row_number() over (order by stime) as rid from @table) t left join ( select *,row_number() over (order by stime) as rid from @table) m on t.rid=m.rid-1 /* name stime etime jianli ---- ---------- ---------- ------------ 王一 2006-01-01 2007-08-06 aaaaaaaaaaaa 王一 2007-08-06 2011-05-06 bbbbbbbbbbb 王一 2011-05-06 NULL cccccccccccc */
update a set Etime=(select top 1 Stime from tb where name=a.name and Stime>a.Stime order by Stime) from tb a
如果你是MSSQL2005版本及以上,可以先利用;with aaa as (select row_number() over(partition by name order by Stime) as rowindex,* from 履历表11)先生成一张CTE,然后利用刚才生成的rowindex字段来选择你需要的记录。
from tb where name=a.name order by Stime>a.Stime order by Stime)
from tb a
declare @table table
(name varchar(4),Stime varchar(10),Etime varchar(10),Jianli varchar(12))
insert into @table
select '王一','2006-01-01','2007-08-06','aaaaaaaaaaaa' union all
select '王一','2007-08-06',null,'bbbbbbbbbbb' union all
select '王一','2011-05-06',null,'cccccccccccc'select t.name,t.stime,
case when t.rid<3 then m.Stime else t.etime end as etime,t.jianli
from (select *,row_number() over (order by stime) as rid from @table) t
left join (
select *,row_number() over (order by stime) as rid from @table) m
on t.rid=m.rid-1
/*
name stime etime jianli
---- ---------- ---------- ------------
王一 2006-01-01 2007-08-06 aaaaaaaaaaaa
王一 2007-08-06 2011-05-06 bbbbbbbbbbb
王一 2011-05-06 NULL cccccccccccc
*/
from tb where name=a.name and Stime>a.Stime order by Stime)
from tb a
2楼提示row_number错误,另外其中m是什么表?在没好办法之前,我要用老本行了。导出为excel,用vba解决
(select row_number() over(partition by name order by Stime) as rowindex,* from 履历表11)先生成一张CTE,然后利用刚才生成的rowindex字段来选择你需要的记录。
胡一 2009-1-1 大连工作
张三 1999-1-1 2000-1-1 桂林工作
张三 2000-10-10 2005-2-6 杭州工作
张三 2000-1-1 2000-10-10 合肥工作
张三 2009-3-6 昆山工作
王小 2002-1-1 2008-1-1 南京工作
王小 2001-1-1 2002-1-1 上海工作
胡一 2001-1-1 2005-1-1 上海工作
王小 2008-1-1 深圳工作
张三 2005-2-6 苏州工作
胡一 2005-1-1 天津工作
还是导出为excel,处理后再导入这是vba代码Sub cc()
For i = 3000 To 2 Step -1a1 = Sheets(1).Cells(i, 1) '此记录姓名
a2 = Sheets(1).Cells(i - 1, 1) '上一记录姓名
b1 = Sheets(1).Cells(i, 2) '此记录起始时间
b2 = Sheets(1).Cells(i - 1, 2)
c1 = Sheets(1).Cells(i, 3) '此记录截止时间
c2 = Sheets(1).Cells(i - 1, 3)If a1 = a2 And c1 = "" And c2 = "" Then
Sheets(1).Cells(i - 1, 3) = b1
a1 = ""
a2 = ""
b1 = ""
b2 = ""
c1 = ""
c2 = ""
Else
a1 = ""
a2 = ""
b1 = ""
b2 = ""
c1 = ""
c2 = ""
End IfNext i
MsgBox "ok"
End Sub