27,580
社区成员
发帖
与我相关
我的任务
分享
create table t1
(
riqi datetime,
a int
)
insert into t1
select '2011-08-10 00:00:01.000',1 union all
select '2011-08-10 01:00:15.000',2 union all
select '2011-08-10 02:02:01.000',3 union all
select '2011-08-10 03:00:01.000',4 union all
select '2011-08-10 04:15:01.000',5 union all
select '2011-08-10 05:05:01.000',6 union all
select '2011-08-10 06:00:01.000',7
select * from t1
;with aaa as
(
select ROW_NUMBER() over(order by riqi) as row,* from t1
)
,bbb as
(
select a.row as row1,a.riqi as riqi1,a.a as a1,b.row as row2,b.riqi as riqi2,b.a as a2
from aaa as a inner join aaa as b on a.row=b.row-1
)
update bbb set a2=a1 where riqi1<DATEADD(HOUR,-1,riqi2)
create table #mytable
(
myid int identity(1,1),
mydate date
)
insert into #mytable
select '2011-2-4' union all
select '2011-3-1' union all
select '2011-2-9' union all
select '2011-5-23' union all
select '2011-5-3' union all
select '2011-2-16' union all
select '2011-2-3' union all
select '2011-7-2'
select*,DATEDIFF (hh ,date1,date2) as 后一天与前一天时间差 from(
select a.mydate as date1,b.mydate as date2 from #mytable a
inner join #mytable b on a.myid+1=b.myid
)a