求一个简单SQL

Question

UseriD   Status    time
1 0 2010-07-01 09:25:40.803
1 4 2010-07-01 09:28:07.650
1 0 2010-07-01 10:06:07.413
1 4 2010-07-01 10:07:17.727
1 0 2010-07-01 10:07:17.760
1 4 2010-07-01 10:08:14.680
2 0 2010-07-01 10:36:07.900
2 4 2010-07-01 10:55:39.540
2 0 2010-07-01 10:58:21.383
2 4 2010-07-01 10:58:52.303
结果已经排序好, 现在有个需求0的时间减去4的时间, 然后userid一样的加起来.
请问这个SQL怎么写?

Answer 1

时间差精度怎么表示？xx毫秒？xx秒？ 还是 hh:mm:ss?

Answer 2

时间差精度到毫秒需要其他表示方式转一下。
SELECT Userid, SUM(case Status when 0 then datediff(ms,time,0) when 4 datediff(ms,0,time) end) from tb group by userid

Answer 3

---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([UseriD] int,[Status] int,[time] datetime)
insert [tb]
select 1,0,'2010-07-01 09:25:40.803' union all
select 1,4,'2010-07-01 09:28:07.650' union all
select 1,0,'2010-07-01 10:06:07.413' union all
select 1,4,'2010-07-01 10:07:17.727' union all
select 1,0,'2010-07-01 10:07:17.760' union all
select 1,4,'2010-07-01 10:08:14.680' union all
select 2,0,'2010-07-01 10:36:07.900' union all
select 2,4,'2010-07-01 10:55:39.540' union all
select 2,0,'2010-07-01 10:58:21.383' union all
select 2,4,'2010-07-01 10:58:52.303'
 
---查询---
select a.userid,sum(datediff(ss,b.[time],a.[time])) [时间差(秒)]
from
(select *,rn=row_number() over(partition by [userid] order by [time]) from tb where status=0) a
join
(select *,rn=row_number() over(partition by [userid] order by [time]) from tb where status=4) b
on 
  a.userid=b.userid and a.rn=b.rn
group by 
  a.userid---结果---
userid      时间差(秒)
----------- -----------
1           -274
2           -1203(2 行受影响)

Answer 4

好像写反了没环境。楼上帮忙测试一下，搞不清除了。。
SELECT Userid, SUM(case Status when 0 then datediff(ms,0,time) when 4 datediff(ms,time,0) end) from tb group by userid

Answer 5

修改一下：由于 datediff(ms,0,[time]) 导致：两个 datetime 列的差别导致了运行时溢出。datediff的返回值是int类型。
如果返回值超出 int 的范围（-2,147,483,648 到 +2,147,483,647），则会返回一个错误。 对于 millisecond，startdate 与 enddate 之间的最大差值为 24 天 20 小时 31 分钟 23.647 秒。 对于 second，最大差值为 68 年。
以前不知道
SELECT Userid, SUM(case [Status] when 0 then datediff(ss,datediff(day,0,[time]),[time]) when 4 then datediff(ss,[time],datediff(day,0,[time])) end) from tb group by userid