27,579
社区成员
发帖
与我相关
我的任务
分享
if object_id('[Table_1]') is not null drop table [Table_1]
create table [Table_1] (ID int,DutyNum int,NAME varchar(4),UPdateTIME datetime)
insert into [Table_1]
select 1,1,'TEST','2014-10-30 14:43:30.000' union all
select 2,1,'TEST','2014-10-30 14:44:30.000'
select * from [Table_1]
SELECT a.*,ISNULL(DATEDIFF(ss,b.UPdateTIME,a.UPdateTIME),0) AS [diff(s)]
FROM Table_1 a
left JOIN table_1 b ON a.id = b.id+1
/*
ID DutyNum NAME UPdateTIME diff(s)
1 1 TEST 2014-10-30 14:43:30.000 0
2 1 TEST 2014-10-30 14:44:30.000 60*/
WITH table1(ID,UPdateTIME) AS (
SELECT 1,Convert(datetime,'2014-10-30 14:43:30.000',120) UNION ALL
SELECT 2,Convert(datetime,'2014-10-30 14:44:30.000',120)
)
SELECT Convert(varchar(12),t2.UPdateTIME - t1.UPdateTIME,114) 间隔
FROM (SELECT UPdateTIME FROM table1 WHERE ID=1) t1,
(SELECT UPdateTIME FROM table1 WHERE ID=2) t2
间隔
------------
00:01:00:000