22,209
社区成员
发帖
与我相关
我的任务
分享
select
b.project,abs(datediff(ss,b.start_dt,a.start_dt))
from
a,b
where
a.id=b.id-1
if object_id('[TB]') is not null drop table [TB]
go
create table [TB] (start_dt datetime,id int,project nvarchar(6))
insert into [TB]
select '2011-12-02 23:30:00',1,'NM1' union all
select '2011-12-05 22:30:00',2,'NM2' union all
select '2011-12-02 14:45:00',3,'NM1'
select * from [TB]
SELECT B.project,ABS(DATEDIFF(ss,B.start_dt,A.start_dt)) AS 用时
FROM TB A
INNER JOIN TB B ON A.id = B.id - 1
/*
project 用时
NM2 255600
NM1 287100*/
create table tb(start_dt datetime,id int,project varchar(10))
insert into tb values('2011-12-02 23:30:00', 1 ,'NM1')
insert into tb values('2011-12-05 22:30:00', 2 ,'NM2')
insert into tb values('2011-12-02 14:45:00', 3 ,'NM1')
go
select t.* , [用时(s)] = abs(datediff(ss,isnull((select top 1 start_dt from tb where id < t.id order by id) , '2011-12-02 07:00:00'),t.start_dt)) from tb t
/*
start_dt id project 用时(s)
------------------------------------------------------ ----------- ---------- -----------
2011-12-02 23:30:00.000 1 NM1 59400
2011-12-05 22:30:00.000 2 NM2 255600
2011-12-02 14:45:00.000 3 NM1 31500
(所影响的行数为 3 行)
*/
select project , sum([用时(s)]) [用时(s)] from
(
select t.* , [用时(s)] = abs(datediff(ss,isnull((select top 1 start_dt from tb where id < t.id order by id) , '2011-12-02 07:00:00'),t.start_dt)) from tb t
) m
group by project
/*
project 用时(s)
---------- -----------
NM1 90900
NM2 255600
(所影响的行数为 2 行)
*/
drop table tb
create table tb(start_dt datetime,id int,project varchar(10))
insert into tb values('2011-12-02 23:30:00', 1 ,'NM1')
insert into tb values('2011-12-05 22:30:00', 2 ,'NM2')
insert into tb values('2011-12-02 14:45:00', 3 ,'NM1')
go
select t.* , [用时(s)] = abs(datediff(ss,isnull((select top 1 start_dt from tb where id < t.id order by id) , '2011-12-02 07:00:00'),t.start_dt)) from tb t
drop table tb
/*
start_dt id project 用时(s)
------------------------------------------------------ ----------- ---------- -----------
2011-12-02 23:30:00.000 1 NM1 59400
2011-12-05 22:30:00.000 2 NM2 255600
2011-12-02 14:45:00.000 3 NM1 31500
(所影响的行数为 3 行)
*/