两条数据间的时间
start_dt id project
2011-12-02 23:30:00 1 NM1
2011-12-05 22:30:00 2 NM2
2011-12-02 14:45:00 3 NM1
如何根据以上的条件,查询每个project的用时。
如project NM2用时等于 2011-12-05 22:30:00 减去 2011-12-02 23:30:00
project NM1用时等于 2011-12-02 23:30:00 减去 2011-12-02 07:00:00(查询开始时间)。
结果是
use_dt project
?? NM2
?? NM1
请大侠们帮帮忙。。。
感激不尽!!
[解决办法]
select t.* , dateadd(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
[解决办法]
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')goselect 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 tdrop table tb/*start_dt id project 用时(s) ------------------------------------------------------ ----------- ---------- ----------- 2011-12-02 23:30:00.000 1 NM1 594002011-12-05 22:30:00.000 2 NM2 2556002011-12-02 14:45:00.000 3 NM1 31500(所影响的行数为 3 行)*/
[解决办法]
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')goselect 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 594002011-12-05 22:30:00.000 2 NM2 2556002011-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) mgroup by project/*project 用时(s) ---------- ----------- NM1 90900NM2 255600(所影响的行数为 2 行)*/drop table tb
[解决办法]
if object_id('[TB]') is not null drop table [TB]gocreate table [TB] (start_dt datetime,id int,project nvarchar(6))insert into [TB]select '2011-12-02 23:30:00',1,'NM1' union allselect '2011-12-05 22:30:00',2,'NM2' union allselect '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 AINNER JOIN TB B ON A.id = B.id - 1/*project 用时NM2 255600NM1 287100*/