Declare @temp table (ID varchar(8),[Time] datetime) insert into @temp select '0001','2010-08-19' union all select '0002','2010-08-27' union all select '0004','2010-08-28' union all select '2333','2010-08-16' union all select '4433','2010-08-13' union all select '8888','2010-08-12' ;with cte as ( select Num = row_number() over (order by [Time]),* from @temp ) select a.ID,b.ID,b.Time from cte a inner join cte b on a.Num = b.Num+1 /* ID ID Time -------- -------- ----------------------- 4433 8888 2010-08-12 00:00:00.000 2333 4433 2010-08-13 00:00:00.000 0001 2333 2010-08-16 00:00:00.000 0002 0001 2010-08-19 00:00:00.000 0004 0002 2010-08-27 00:00:00.000(5 行受影响) */
if object_id('tb')>0 drop table tb create table tb ( [工作内容] varchar(20), [工作时间] datetime ) insert into tb select '0001', '2010-08-19' union all select '0002', '2010-08-27' union all select '0004', '2010-08-28' union all select '2333', '2010-08-16' union all select '4433', '2010-08-13' union all select '8888', '2010-08-12'select a.[工作内容], (select top 1 b.[工作内容] from tb as b where b.工作时间<a.工作时间 order by b.工作时间 desc) as '上一个工作内容' , a.工作时间 from tb as a 结果0001 2333 2010-08-19 00:00:00.000 0002 0001 2010-08-27 00:00:00.000 0004 0002 2010-08-28 00:00:00.000 2333 4433 2010-08-16 00:00:00.000 4433 8888 2010-08-13 00:00:00.000 8888 NULL 2010-08-12 00:00:00.000
--left join 效果 select a.ID,b.ID,b.Time from cte a left join cte b on a.Num = b.Num+1 /* ID ID Time -------- -------- ----------------------- 8888 NULL NULL 4433 8888 2010-08-12 00:00:00.000 2333 4433 2010-08-13 00:00:00.000 0001 2333 2010-08-16 00:00:00.000 0002 0001 2010-08-19 00:00:00.000 0004 0002 2010-08-27 00:00:00.000(5 行受影响) */
if object_id('tb')>0 drop table tb create table tb ( [工作内容] varchar(20), [工作时间] datetime ) insert into tb select '0001', '2010-08-19' union all select '0002', '2010-08-27' union all select '0004', '2010-08-28' union all select '2333', '2010-08-16' union all select '4433', '2010-08-13' union all select '8888', '2010-08-12'select a.[工作内容], (select TOP 1 b.[工作内容] from tb as b where b.工作时间<a.工作时间 order by b.工作时间 desc) as '上一个工作内容' , a.工作时间 from tb as aORDER BY a.工作时间 DESC
insert into @temp
select '0001','2010-08-19'
union all select '0002','2010-08-27'
union all select '0004','2010-08-28'
union all select '2333','2010-08-16'
union all select '4433','2010-08-13'
union all select '8888','2010-08-12'
;with cte as
(
select Num = row_number() over (order by [Time]),* from @temp
)
select a.ID,b.ID,b.Time from cte a inner join cte b
on a.Num = b.Num+1
/*
ID ID Time
-------- -------- -----------------------
4433 8888 2010-08-12 00:00:00.000
2333 4433 2010-08-13 00:00:00.000
0001 2333 2010-08-16 00:00:00.000
0002 0001 2010-08-19 00:00:00.000
0004 0002 2010-08-27 00:00:00.000(5 行受影响)
*/
if object_id('tb')>0
drop table tb
create table tb
(
[工作内容] varchar(20),
[工作时间] datetime
)
insert into tb
select '0001', '2010-08-19'
union all
select '0002', '2010-08-27'
union all
select '0004', '2010-08-28'
union all
select '2333', '2010-08-16'
union all
select '4433', '2010-08-13'
union all
select '8888', '2010-08-12'select a.[工作内容],
(select top 1 b.[工作内容]
from tb as b where b.工作时间<a.工作时间 order by b.工作时间 desc) as '上一个工作内容' , a.工作时间 from tb as a 结果0001 2333 2010-08-19 00:00:00.000
0002 0001 2010-08-27 00:00:00.000
0004 0002 2010-08-28 00:00:00.000
2333 4433 2010-08-16 00:00:00.000
4433 8888 2010-08-13 00:00:00.000
8888 NULL 2010-08-12 00:00:00.000
select a.ID,b.ID,b.Time from cte a left join cte b
on a.Num = b.Num+1
/*
ID ID Time
-------- -------- -----------------------
8888 NULL NULL
4433 8888 2010-08-12 00:00:00.000
2333 4433 2010-08-13 00:00:00.000
0001 2333 2010-08-16 00:00:00.000
0002 0001 2010-08-19 00:00:00.000
0004 0002 2010-08-27 00:00:00.000(5 行受影响)
*/
drop table tb
create table tb
(
[工作内容] varchar(20),
[工作时间] datetime
)
insert into tb
select '0001', '2010-08-19'
union all
select '0002', '2010-08-27'
union all
select '0004', '2010-08-28'
union all
select '2333', '2010-08-16'
union all
select '4433', '2010-08-13'
union all
select '8888', '2010-08-12'select a.[工作内容],
(select TOP 1 b.[工作内容]
from tb as b where b.工作时间<a.工作时间 order by b.工作时间 desc) as '上一个工作内容' , a.工作时间 from tb as aORDER BY a.工作时间 DESC