分组去掉相邻重复的记录后求累计时间差
分组去掉相邻重复的记录后求累计时间差
字段以及数据是：（表1）
Devid Systime state
1001 08:30:10 0
1001 09:25:20 0
1001 10:10:10 1 t1 （即第一条state=1的记录）
1001 10:10:20 1
1001 10:10:40 1
1001 10:11:05 0 t2 （即第一条state=0的记录）
1001 10:11:30 0
1001 10:11:40 1 t3 （即state=0之后第一条state=1的记录）
1001 10:12:10 1
1001 10:12:21 0 t4 （即state=1之后第一条state=0的记录）
1001 10:12:30 0
1001 10:12:50 1 t5 （同上）
1002 10:10:11 0
1002 10:10:23 1 t1' （同上）
1002 10:10:40 1
1002 10:11:05 0 t2'
1002 10:11:40 1 t3'
1002 10:12:21 0 t4'
1002 10:12:30 0
100210:15:30 1 t5'
1002 10:16:25 1 t6'
1003 10:10:23 1 t11' （同上）
1003 10:11:05 0 t12'
1003 10:11:20 0
1003 10:11:40 1 t13'
1003 10:12:21 0 t14'
1003 10:12:30 0
要求得到的结果如下:
devid systime state totaltime
1001 08:30:10 0 0
1001 10:13:11 1 (t2-t1)+(t4-t3)+(10*60) //从第一条state=1的开始计算，若最后仅一条state=1，则结果+10分钟
1002 10:10:11 0 0
1002 10:16:25 1 (t2'-t1')+(t4'-t3')+ +(t6'-t5') //若最后连续state=1，则要计算连续state=1的时间差
1003 10:10:23 1 0
1003 10:12:30 0 (t12'-t11')+(t14'-t13')
计算的原理：供参考
去掉相邻state相同的记录，从第一条state=1的开始计算，下一次state=0的时间减去上一次state=1的时间的累积和；
若最后仅一条state=1，则结果+10分钟；若最后几条连续state=1，则要计算连续state=1的时间差。
[解决办法]
SQL code---测试数据---if object_id('[tb]') is not null drop table [tb]gocreate table [tb]([value1] int,[value2] int)insert [tb]select 1,12 union allselect 1,13 union allselect 1,23 union allselect 0,14 union allselect 0,15 union allselect 1,16 union allselect 0,23 union allselect 0,22 union allselect 1,21 union allselect 1,12 ---查询---select id=identity(int,1,1),* into # from [tb]select value1,value2 from # t where not exists(select * from # where value1=t.value1 and id=t.id-1)
[解决办法]
CREATE TABLE TBTEST(Devid INT, Systime VARCHAR(20), state INT)
INSERT TBTEST
SELECT 1001 , '08:30:10' , 0 UNION ALL
SELECT 1001 , '09:25:20' , 0 UNION ALL
SELECT 1001 , '10:10:10 ' , 1 UNION ALL
SELECT 1001 , '10:10:20' , 1 UNION ALL
SELECT 1001 , '10:10:40' , 1 UNION ALL
SELECT 1001 , '10:11:05' , 0 UNION ALL
SELECT 1001 , '10:11:30' , 0 UNION ALL
SELECT 1001 , '10:11:40' , 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 1002 , '10:10:11' , 0 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:40' , 1 UNION ALL
SELECT 1002 , ' 10:12:21' , 0 UNION ALL
SELECT 1002 , ' 10:12:30' , 0
--SELECT * FROM TBTEST
SELECT ID=IDENTITY(INT,1,1),* INTO #T FROM TBTEST
SELECT IDD=IDENTITY(INT,1,1),Devid,Systime,state INTO #TT
FROM
(SELECT ID,Devid,Systime,state FROM #T T
WHERE NOT EXISTS(SELECT 1 FROM #T WHERE Devid=T.Devid AND state=T.state AND ID=T.ID-1)) AS T
WHERE ID NOT IN (SELECT MIN(ID) FROM #T WHERE state=0 GROUP BY Devid)
--SELECT * FROM #tt
SELECT Devid,SUM(SSTIME)SSTIME FROM
(
SELECT T1.Devid,DATEDIFF(SS,CONVERT(DATETIME,T2.Systime),CONVERT(DATETIME,T1.Systime))AS SSTIME
FROM #TT T1,#TT T2 WHERE T1.IDD=T2.IDD+1 AND T1.state=T2.state-1
UNION ALL
SELECT Devid,10*60 FROM #TT WHERE IDD IN (SELECT MAX(#TT.IDD)IDD
FROM #TT WHERE IDD%2 <>0 AND STATE=1 GROUP BY Devid )) AS T GROUP BY Devid
Devid SSTIME
----------- -----------
1001 696
1002 83
（所影响的行数为 2 行）
---------------------------------
-- Author: htl258(Tony)
-- Date : 2009-08-10 10:30:38
---------------------------------
--> 生成测试数据表:tb
If not object_id('[tb]') is null
Drop table [tb]
Go
Create table [tb]([Devid] int,[Systime] varchar(10),[state] int)
Insert tb
Select 1001,'08:30:10',0 union all
Select 1001,'09:25:20',0 union all
Select 1001,'10:10:10',1 union all
Select 1001,'10:10:20',1 union all
Select 1001,'10:10:40',1 union all
Select 1001,'10:11:05',0 union all
Select 1001,'10:11:30',0 union all
Select 1001,'10:11:40',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 1002,'10:10:11',0 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:40',1 union all
Select 1002,'10:12:21',0 union all
Select 1002,'10:12:30',0 union all
Select 1002,'10:15:30',1 union all
Select 1002,'10:16:25',1 union all
Select 1003,'10:10:23',1 union all
Select 1003,'10:11:05',0 union all
Select 1003,'10:11:20',0 union all
Select 1003,'10:11:40',1 union all
Select 1003,'10:12:21',0 union all
Select 1003,'10:12:30',0
Go
--Select * from tb
-->SQL查询如下:
--1.建立临时表
If not object_id('tempdb..#') is null
Drop table #
Go
select id=identity(int),* into # from tb
--2.在临时表上增加一辅助列
alter table # add fid int
go
--3.更新辅助列信息
declare @i int,@j int,@k int
update # set
fid=@i,
@i=case when Devid=@j and state=@k then isnull(@i,0) else isnull(@i,0)+1 end,
@j=devid,
@k=state
--4.更新state,fid值
update a set
state=1,fid=fid-1
from # a
where (select count(1) from # where devid=a.devid and id <=a.id)>1
and not exists(select 1 from # where devid=a.devid and fid=a.fid and id <a.id)
and state=0
--5.删除state为0的多余记录
delete a
from # a
where state=0
and (select count(1) from # where devid=a.devid and id <=a.id)>1
and (select count(1) from # where devid=a.devid and id>=a.id)>1
--6.查询结果
select *
from (
select devid,systime,state,0 as totaltime from # a
where (select count(1) from # where devid=a.devid and id <=a.id)=1
union all
select a.devid,a.systime,a.state,b.totaltime
from # a
join (
select devid,sum(timedif) as totaltime
from (
select devid,
case datediff(ss,min(systime),max(systime)) when 0 then 10*60
else datediff(ss,min(systime),max(systime)) end as timedif,
fid
from #
where state>0
group by devid,fid
) as t
group by devid
) as b
on a.devid=b.devid
where (select count(1) from # where devid=a.devid and id>=a.id)=1
) as t
order by 1,2
/*
devid systime state totaltime
----------- ---------- ----------- -----------
1001 08:30:10 0 0
1001 10:12:50 1 696
1002 10:10:11 0 0
1002 10:16:25 1 138
1003 10:10:23 1 0
1003 10:12:30 0 83
(6 行受影响)
*/