sql server 统计计算时间差

Question

有一个数据库表，表名为A
字段以及数据是：
   Devid    Systime        state
   1001      10:10:10        00000010        t1   （即第一条state 第2位=1的记录）
   1001      10:10:20        00000010
   1001      10:10:28        00000010
   1001      10:10:40        00000010
   1001      10:11:05        00000000        t2   （即第一条state第2位=0的记录）
   1001      10:11:20        00000000
   1001      10:11:30        00000000
   1001      10:11:40        00000010        t3   （即state=0之后第一条state第2位=1的记录）
   1001      10:11:52        00000010
   1001      10:12:10        00000010
   1001      10:12:21        00000000        t4   （即state=1之后第一条state第2位=0的记录）
   1001      10:12:30        00000000
   1001      10:12:50        00000010        t5   （同上）
   1001      10:13:11        00000000        t6   （同上）   1002      10:10:11        00000010        t1'   （同上）
   1002      10:10:23        00000010
   1002      10:10:40        00000010
   1002      10:11:05        00000000        t2'
   1002      10:11:20        00000000
   1002      10:11:30        00000000
   1002      10:11:40        00000010        t3'
   1002      10:11:52        00000010
   1002      10:12:10        00000010
   1002      10:12:21        00000000        t4'
   1002      10:12:30        00000000要求得到的结果如下:
   devid     systime         totaltime
   1001      10:10:10        0
   1001      10:13:11        (t2-t1)+(t4-t3)+(t6-t5)
   1002      10:10:11        0
   1002      10:12:30        (t2'-t1')+(t4'-t3')
为节省大家时间，数据生成如下：
declare @A table(Devid varchar(10),Systime varchar(10),state int)
insert into @t select 1001,'10:10:10',1
union all      select 1001,'10:10:20',1
union all      select 1001,'10:10:28',1
union all      select 1001,'10:10:40',1
union all      select 1001,'10:11:05',0
union all      select 1001,'10:11:20',0
union all      select 1001,'10:11:30',0
union all      select 1001,'10:11:40',1
union all      select 1001,'10:11:52',1
union all      select 1001,'10:12:10',1
union all      select 1001,'10:12:21',0
union all      select 1001,'10:12:30',0
union all      select 1001,'10:12:50',1
union all      select 1001,'10:13:11',0
union all      select 1002,'10:10:11',1
union all      select 1002,'10:10:23',1
union all      select 1002,'10:10:40',1
union all      select 1002,'10:11:05',0
union all      select 1002,'10:11:20',0
union all      select 1002,'10:11:30',0
union all      select 1002,'10:11:40',1
union all      select 1002,'10:11:52',1
union all      select 1002,'10:12:10',1
union all      select 1002,'10:12:21',0
union all      select 1002,'10:12:30',0

Answer 1

t1,t2指记录的时间点systime的值

Answer 2

数据生成的时候，state字段数据是错误的，应该是“00000010”或“00000000”，而不是“1”和“0”，请高手在解答的时候避开该错误。

Answer 3

declare @t table(Devid varchar(10),Systime datetime,state int) 
insert into @t select 1001,'10:10:10',1 
union all      select 1001,'10:10:20',1 
union all      select 1001,'10:10:28',1 
union all      select 1001,'10:10:40',1 
union all      select 1001,'10:11:05',0 
union all      select 1001,'10:11:20',0 
union all      select 1001,'10:11:30',0 
union all      select 1001,'10:11:40',1 
union all      select 1001,'10:11:52',1 
union all      select 1001,'10:12:10',1 
union all      select 1001,'10:12:21',0 
union all      select 1001,'10:12:30',0 
union all      select 1001,'10:12:50',1 
union all      select 1001,'10:13:11',0 
union all      select 1002,'10:10:11',1 
union all      select 1002,'10:10:23',1 
union all      select 1002,'10:10:40',1 
union all      select 1002,'10:11:05',0 
union all      select 1002,'10:11:20',0 
union all      select 1002,'10:11:30',0 
union all      select 1002,'10:11:40',1 
union all      select 1002,'10:11:52',1 
union all      select 1002,'10:12:10',1 
union all      select 1002,'10:12:21',0 
union all      select 1002,'10:12:30',0;
with wang as 
 (select row=row_number() over(order by getdate()),* from @t),
wang1 as
  (select * from wang t where not exists(select 1 from wang where state=t.state and row=t.row-1))
select devid,min(systime),totaltime=0
from wang1
group by devid
union all
select devid,max(systime),totaltime=sum(case when state=0 then cast(cast(systime as varbinary )as int)else -cast(cast(systime as varbinary )as int) end)
from wang1
group by deviddevid (无列名) totaltime
1001 1900-01-01 10:10:10.000 0
1002 1900-01-01 10:10:11.000 0
1001 1900-01-01 10:13:11.000 35100
1002 1900-01-01 10:12:21.000 28500最终的时间应该是秒的，没有转化

Answer 4

create table POO (Devid varchar(10),Systime varchar(10),state int) 
insert into poo select 1001,'10:10:10',1 
union all      select 1001,'10:10:20',1 
union all      select 1001,'10:10:28',1 
union all      select 1001,'10:10:40',1 
union all      select 1001,'10:11:05',0 
union all      select 1001,'10:11:20',0 
union all      select 1001,'10:11:30',0 
union all      select 1001,'10:11:40',1 
union all      select 1001,'10:11:52',1 
union all      select 1001,'10:12:10',1 
union all      select 1001,'10:12:21',0 
union all      select 1001,'10:12:30',0 
union all      select 1001,'10:12:50',1 
union all      select 1001,'10:13:11',0 
union all      select 1002,'10:10:11',1 
union all      select 1002,'10:10:23',1 
union all      select 1002,'10:10:40',1 
union all      select 1002,'10:11:05',0 
union all      select 1002,'10:11:20',0 
union all      select 1002,'10:11:30',0 
union all      select 1002,'10:11:40',1 
union all      select 1002,'10:11:52',1 
union all      select 1002,'10:12:10',1 
union all      select 1002,'10:12:21',0 
union all      select 1002,'10:12:30',0 select
  *,identity(int,1,1) as id 
into #lp
from POO  
order by Devid,Systime select *,identity(int,1,1) as id  into kg
from (
select Devid , Systime,state 
from #lp t
where Id=1
union all
select Devid , Systime,state 
from #lp t
where exists(select * from #lp where t.state<>state and t.id=ID+1)) pselect * from(
select 
Devid ,
Systime=MIN(Systime),
totaltime=0
from kg
group by Devid
union all
select 
Devid ,
Systime=Max(Systime)
,totaltime=(select  SUM(cha)
from (select DATEDIFF(second,(select systime from kg where id=k.id and k.Devid=Devid),(select systime from kg where id=k.id+1 and k.Devid=Devid)) as cha
from kg k
where ID%2=1 and Devid=q.Devid ) p )
from kg q
group by Devid) k
order by Devid 
/*
Devid      Systime    totaltime
---------- ---------- -----------
1001       10:10:10   0
1001       10:13:11   117
1002       10:10:11   0
1002       10:12:21   95*/

Answer 5

楼主totaltime 的单位 我这里是 秒

Answer 6

汗，写了一个居然和8楼的几乎是一样的create table tb(Devid varchar(10),Systime datetime,state int) 
insert into tb select 1001,'10:10:10',1 
union all      select 1001,'10:10:20',1 
union all      select 1001,'10:10:28',1 
union all      select 1001,'10:10:40',1 
union all      select 1001,'10:11:05',0 
union all      select 1001,'10:11:20',0 
union all      select 1001,'10:11:30',0 
union all      select 1001,'10:11:40',1 
union all      select 1001,'10:11:52',1 
union all      select 1001,'10:12:10',1 
union all      select 1001,'10:12:21',0 
union all      select 1001,'10:12:30',0 
union all      select 1001,'10:12:50',1 
union all      select 1001,'10:13:11',0 
union all      select 1002,'10:10:11',1 
union all      select 1002,'10:10:23',1 
union all      select 1002,'10:10:40',1 
union all      select 1002,'10:11:05',0 
union all      select 1002,'10:11:20',0 
union all      select 1002,'10:11:30',0 
union all      select 1002,'10:11:40',1 
union all      select 1002,'10:11:52',1 
union all      select 1002,'10:12:10',1 
union all      select 1002,'10:12:21',0 
union all      select 1002,'10:12:30',0 select *,px1=(select count(1)+1 from tb where devid=t.devid and systime<t.systime) into #1 from tb t
select * into #2 from #1 t where not exists(select 1 from #1 where devid=t.devid and state=t.state and px1=t.px1-1)
select * into #3 from #1 t where not exists(select 1 from #1 where devid=t.devid and state=t.state and px1=t.px1+1)select 
  a.devid,
  a.systime,
  a.state,
  a.px2
into #4
from
  (select *,px2=(select count(1)+1 from #2 where devid=t.devid and state=t.state and px1<t.px1) from #2 t) a,
  (select *,px2=(select count(1)+1 from #3 where devid=t.devid and state=t.state and px1<t.px1) from #3 t) b
where
  a.devid=b.devid 
and 
  a.state=b.state
and
  a.px2=b.px2select 
  a.devid,
  convert(varchar(8),min(a.systime),108) as systime,
  0 as [totaltime(秒)] 
from
  (select * from #4 where state=1) a,
  (select * from #4 where state=0) b
where
  a.devid=b.devid
and
  a.px2=b.px2
group by 
  a.devid
union all
select 
  a.devid,
  convert(varchar(8),max(b.systime),108),
  sum(datediff(ss,a.systime,b.systime))
from
  (select * from #4 where state=1) a,
  (select * from #4 where state=0) b
where
  a.devid=b.devid
and
  a.px2=b.px2
group by 
  a.devid
order by
  a.devid/**
devid      systime    totaltime(秒) 
---------- ---------- ------------ 
1001       10:10:10   0
1001       10:13:11   117
1002       10:10:11   0
1002       10:12:21   95（所影响的行数为 4 行）
**/drop table tb,#1,#2,#3,#4

Answer 7

修正一下
declare @t table(Devid varchar(10),Systime datetime,state int) 
insert into @t select 1001,'10:10:10',1 
union all      select 1001,'10:10:20',1 
union all      select 1001,'10:10:28',1 
union all      select 1001,'10:10:40',1 
union all      select 1001,'10:11:05',0 
union all      select 1001,'10:11:20',0 
union all      select 1001,'10:11:30',0 
union all      select 1001,'10:11:40',1 
union all      select 1001,'10:11:52',1 
union all      select 1001,'10:12:10',1 
union all      select 1001,'10:12:21',0 
union all      select 1001,'10:12:30',0 
union all      select 1001,'10:12:50',1 
union all      select 1001,'10:13:11',0 
union all      select 1002,'10:10:11',1 
union all      select 1002,'10:10:23',1 
union all      select 1002,'10:10:40',1 
union all      select 1002,'10:11:05',0 
union all      select 1002,'10:11:20',0 
union all      select 1002,'10:11:30',0 
union all      select 1002,'10:11:40',1 
union all      select 1002,'10:11:52',1 
union all      select 1002,'10:12:10',1 
union all      select 1002,'10:12:21',0 
union all      select 1002,'10:12:30',0;
with wang as 
 (select row=row_number() over(order by getdate()),* from @t),
wang1 as
  (select * from wang t where not exists(select 1 from wang where state=t.state and row=t.row-1)),
wang2 as
  (select id=row_number() over(order by getdate()),* from wang1),
wang3 as
  (select s.*,datediff(ss,s.systime,t.systime)  totaltime
   from wang2 s ,wang2 t
   where s.id=t.id-1 and s.id%2=1 and s.devid=t.devid
  )select devid,min(systime),totaltime=0
from wang3
group by devid
union all
select devid,max(systime),totaltime=sum(totaltime)
from wang3
group by devid
order by devid

Answer 8

declare @t table(Devid varchar(10),Systime datetime,state int) 
insert into @t select 1001,'10:10:10',1 
union all      select 1001,'10:10:20',1 
union all      select 1001,'10:10:28',1 
union all      select 1001,'10:10:40',1 
union all      select 1001,'10:11:05',0 
union all      select 1001,'10:11:20',0 
union all      select 1001,'10:11:30',0 
union all      select 1001,'10:11:40',1 
union all      select 1001,'10:11:52',1 
union all      select 1001,'10:12:10',1 
union all      select 1001,'10:12:21',0 
union all      select 1001,'10:12:30',0 
union all      select 1001,'10:12:50',1 
union all      select 1001,'10:13:11',0 
union all      select 1002,'10:10:11',1 
union all      select 1002,'10:10:23',1 
union all      select 1002,'10:10:40',1 
union all      select 1002,'10:11:05',0 
union all      select 1002,'10:11:20',0 
union all      select 1002,'10:11:30',0 
union all      select 1002,'10:11:40',1 
union all      select 1002,'10:11:52',1 
union all      select 1002,'10:12:10',1 
union all      select 1002,'10:12:21',0 
union all      select 1002,'10:12:30',0;
with wang as 
 (select row=row_number() over(order by getdate()),* from @t),
wang1 as
  (select * from wang t where not exists(select 1 from wang where state=t.state and row=t.row-1)),
wang2 as
  (select id=row_number() over(order by getdate()),* from wang1),
wang3 as
  (select s.*,datediff(ss,s.systime,t.systime)  totaltime
   from wang2 s ,wang2 t
   where s.id=t.id-1 and s.id%2=1 and s.devid=t.devid
  )select devid,systime=min(systime),totaltime=0
from wang3
group by devid
union all
select devid,max(systime),totaltime=sum(totaltime)
from wang3
group by devid
order by deviddevid systime totaltime
1001 1900-01-01 10:10:10.000 0
1001 1900-01-01 10:12:50.000 117
1002 1900-01-01 10:10:11.000 0
1002 1900-01-01 10:11:40.000 95

Answer 9

  devid    systime        totaltime 
  1001      10:10:10        0 
  1001      10:13:11        (t2-t1)+(t4-t3)+(t6-t5) 
  1002      10:10:11        0 
  1002      10:12:30        (t2'-t1')+(t4'-t3')  -- 好像楼主这条时间和你们的结果都一样嘛

Answer 10

确实，再修正一下
[
declare @t table(Devid varchar(10),Systime datetime,state int) 
insert into @t select 1001,'10:10:10',1 
union all      select 1001,'10:10:20',1 
union all      select 1001,'10:10:28',1 
union all      select 1001,'10:10:40',1 
union all      select 1001,'10:11:05',0 
union all      select 1001,'10:11:20',0 
union all      select 1001,'10:11:30',0 
union all      select 1001,'10:11:40',1 
union all      select 1001,'10:11:52',1 
union all      select 1001,'10:12:10',1 
union all      select 1001,'10:12:21',0 
union all      select 1001,'10:12:30',0 
union all      select 1001,'10:12:50',1 
union all      select 1001,'10:13:11',0 
union all      select 1002,'10:10:11',1 
union all      select 1002,'10:10:23',1 
union all      select 1002,'10:10:40',1 
union all      select 1002,'10:11:05',0 
union all      select 1002,'10:11:20',0 
union all      select 1002,'10:11:30',0 
union all      select 1002,'10:11:40',1 
union all      select 1002,'10:11:52',1 
union all      select 1002,'10:12:10',1 
union all      select 1002,'10:12:21',0 
union all      select 1002,'10:12:30',0;
with wang as 
 (select row=row_number() over(order by getdate()),* from @t),
wang1 as
  (select * from wang t where not exists(select 1 from wang where state=t.state and row=t.row-1)
   union 
   select * from wang t where not exists(select 1 from wang where devid=t.devid and systime>t.systime)),
wang2 as
  (select id=row_number() over(order by getdate()),* from wang1),
wang3 as
  (select s.*,datediff(ss,s.systime,t.systime)  totaltime
   from wang2 s left join wang2 t
   on s.id=t.id-1 and s.id%2=1 and s.devid=t.devid
  )select devid,systime=min(systime),totaltime=0
from wang3
group by devid
union all
select devid,max(systime),totaltime=sum(totaltime)
from wang3
group by devid
order by deviddevid systime totaltime
1001 1900-01-01 10:10:10.000 0
1001 1900-01-01 10:13:11.000 117
1002 1900-01-01 10:10:11.000 0
1002 1900-01-01 10:12:30.000 95