请教一条sql语句如何写

我有以下表结构

id   status           time
1     a         2010-2-21 08:13:23
2     b 2010-2-21 08:15:10
3     a 2010-2-21 08:19:29
4     b 2010-2-21 09:13:30
5     a 2010-2-21 10:13:49


我想得到以下结果，以id 升序来处理，最早一个status为b和a的time时间差，秒为单位，即id=2和id=1的那两条记录，然后如此类推再加上下一个status：b和a 的time时间差（秒为单位），直到最后一个status=a的记录的时候，如果没有下一条status为b的time作参考计算时间差，那么直接取当前服务器时间来获得时间差，最后获得时间差的总和，那么如何使用一条sql语句获得此结果？

23 个解决方案

#1

---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[status] varchar(1),[time] datetime)
insert [tb]
select 1,'a','2010-2-21 08:13:23' union all
select 2,'b','2010-2-21 08:15:10' union all
select 3,'a','2010-2-21 08:19:29' union all
select 4,'b','2010-2-21 09:13:30' union all
select 5,'a','2010-2-21 10:13:49'
 
---查询---
select 
  sum(datediff(ss,a.[time],isnull(b.[time],getdate()))) as [时间差总和(秒)]
from
(select *,px=(select count(1) from tb where status='a' and [time]<t.[time]) from tb t where status='a') a
left join
(select *,px=(select count(1) from tb where status='b' and [time]<t.[time]) from tb t where status='b') b
on a.px=b.px


---结果---
时间差总和(秒)    
----------- 
44763

（所影响的行数为 1 行）

#2

引用 1 楼 josy 的回复:

SQL code---测试数据---ifobject_id('[tb]')isnotnulldroptable[tb]gocreatetable[tb]([id]int,[status]varchar(1),[time]datetime)insert[tb]select1,'a','2010-2-21 08:13:23'unionallselect2,'b','2010-2-21 08:15:10?-

...........

#3

select sum(case when [status]='a' then datediff(ss,[time],getdate())
 else datediff(ss,getdate(),[time]) end) [sum] from tab

#4

该回复于2010-02-23 09:43:29被版主删除

#5

引用楼主 marklr 的回复:

我有以下表结构

id  status          time
1    a        2010-2-21 08:13:23
2    b 2010-2-21 08:15:10
3    a 2010-2-21 08:19:29
4    b 2010-2-21 09:13:30
5    a 2010-2-21 10:13:49


我想得到以下结果，以id 升序来处理，最早一个status为b和a的time时间差，秒为单位，即id=2和id=1的那两条记录，然后如此类推再加上下一个status：b和a 的time时间差（秒为单位），直到最后一个status=a的记录的时候，如果没有下一条status为b的time作参考计算时间差，那么直接取当前服务器时间来获得时间差，最后获得时间差的总和，那么如何使用一条sql语句获得此结果？

status          

#6

--一个笨方法
--借下树人的数据
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[status] varchar(1),[time] datetime)
insert [tb]
select 1,'a','2010-2-21 08:13:23' union all
select 2,'b','2010-2-21 08:15:10' union all
select 3,'a','2010-2-21 08:19:29' union all
select 4,'b','2010-2-21 09:13:30' union all
select 5,'a','2010-2-21 10:13:49'


--设置一个时间
declare @begin_time datetime
set @begin_time = '2010-02-01'

select case when (select count(status) from tb where status = 'a') = 
                 (select count(status) from tb where status = 'b')
            then (select sum(datediff(ss,time,'2010-02-01')) from tb where status = 'b') - 
                 (select sum(datediff(ss,time,@begin_time)) from tb where status = 'a')
       else datediff(second,getdate(),@begin_time) + 
            (select sum(datediff(ss,time,@begin_time)) from tb where status = 'b') - 
            (select sum(datediff(ss,time,@begin_time)) from tb where status = 'a')
       end

#7

--b - a = (b-c) - (a - c)
--c 是 getdate()
--为啥我想不到呢？

引用 3 楼 xman_78tom 的回复:

SQL codeselectsum(casewhen[status]='a'thendatediff(ss,[time],getdate())elsedatediff(ss,getdate(),[time])end)[sum]from tab

#8

引用 3 楼 xman_78tom 的回复:

SQL codeselectsum(casewhen[status]='a'thendatediff(ss,[time],getdate())elsedatediff(ss,getdate(),[time])end)[sum]from tab

你这个写得和我要求的结果不一样吧？

#9

--你是想要负数的时间差？调转参数即可
select sum(case when [status]='a' then datediff(ss,getdate(),[time])
 else datediff(ss,[time],getdate()) end) [sum] from tab

引用 8 楼 marklr 的回复:

引用 3 楼 xman_78tom 的回复:SQL codeselectsum(casewhen[status]='a'thendatediff(ss,[time],getdate())elsedatediff(ss,getdate(),[time])end)[sum]from tab

你这个写得和我要求的结果不一样吧？

#10

我要求的是按照 id升序排序，从第一条记录开始，每2条status分别为b和a的记录的time字段所记录时间的时间差累加，如果最后一条status为a的记录没有 下一条status为b的记录来计算时间差，则直接取当前服务器时间来计算时间差，最后得出汇总结果

#11

该回复于2010-02-23 16:36:49被版主删除

#12

to ： (百年树人)  ，我想要的结果是这点样的，我举个例子吧

例如
id  status          time 
1    a         2010-2-21 08:13:23 
2    b          2010-2-21 08:15:10
3    a  2010-2-21 08:19:29 
4    b          2010-2-21 09:13:30 
5    a  2010-2-21 10:13:49 

为了方便举例 ：以分钟来计算

记录
 id 1和2   时间差  2分钟
  id 3和4  时间差  6分钟
   id 5      时间差  7分钟 (由于没有下条状态为b的记录参考，直接取服务器时间，假设服务器当前时间  2010-2-21  10:20:22）


那么最后的汇总结果就是 15分钟，那sql 应该怎么写？

#13

to ： (百年树人)  ，我想要的结果是这点样的，我举个例子吧

例如
id  status          time 
1    a         2010-2-21 08:13:23 
2    b          2010-2-21 08:15:10
3    a  2010-2-21 09:13:29 
4    b          2010-2-21 09:19:30 
5    a  2010-2-21 10:13:49 

为了方便举例 ：以分钟来计算

记录
 id 1和2   时间差  2分钟
  id 3和4  时间差  6分钟
   id 5      时间差  7分钟 (由于没有下条状态为b的记录参考，直接取服务器时间，假设服务器当前时间  2010-2-21  10:20:22）


那么最后的汇总结果就是 15分钟，那sql 应该怎么写？

#14

我的结果是按照你说的来的

select 
  a.id,b.id,datediff(mi,a.[time],isnull(b.[time],getdate())) as [时间差(分钟)]
from
(select *,px=(select count(1) from tb where status='a' and [time]<t.[time]) from tb t where status='a') a
left join
(select *,px=(select count(1) from tb where status='b' and [time]<t.[time]) from tb t where status='b') b
on a.px=b.px
/**
id          id          时间差(分钟)     
----------- ----------- ----------- 
1           2           2
3           4           54
5           NULL        1487

（所影响的行数为 3 行）
**/

select 
  sum(datediff(mi,a.[time],isnull(b.[time],getdate()))) as [时间差总和(分钟)]
from
(select *,px=(select count(1) from tb where status='a' and [time]<t.[time]) from tb t where status='a') a
left join
(select *,px=(select count(1) from tb where status='b' and [time]<t.[time]) from tb t where status='b') b
on a.px=b.px

/**
时间差总和(分钟)   
----------- 
1544

（所影响的行数为 1 行）
**/

#15

我也觉得没错，难道楼主忽略了服务器的时间是一直在增大的？

引用 14 楼 josy 的回复:

我的结果是按照你说的来的
SQL codeselect 
  a.id,b.id,datediff(mi,a.[time],isnull(b.[time],getdate()))as[时间差(分钟)]from
(select*,px=(selectcount(1)from tbwhere status='a'and[time]<t.[time])from tb twhere status='a') aleftjoin
(select*,px=(selectcount(1)from tbwhere status='b'and[time]<t.[time])from tb twhere status='b') bon a.px=b.px/**
id          id          时间差(分钟)     
----------- ----------- ----------- 
1           2           2
3           4           54
5           NULL        1487

（所影响的行数为 3 行）
**/selectsum(datediff(mi,a.[time],isnull(b.[time],getdate())))as[时间差总和(分钟)]from
(select*,px=(selectcount(1)from tbwhere status='a'and[time]<t.[time])from tb twhere status='a') aleftjoin
(select*,px=(selectcount(1)from tbwhere status='b'and[time]<t.[time])from tb twhere status='b') bon a.px=b.px/**
时间差总和(分钟)   
----------- 
1544

（所影响的行数为 1 行）
**/

#16

好的，我再测试看看 ！

#17

-- 简单的数学推理
select [id],[status],[time],
(case when [status]='a' then datediff(mi,[time],getdate())
 else datediff(mi,getdate(),[time]) end) [time],
row_number() over (partition by [status] order by [time]) grp
from tb order by grp
/*
1	a	2010-02-21 08:13:23.000	1722	1
2	b	2010-02-21 08:15:10.000	-1720	1
4	b	2010-02-21 09:13:30.000	-1662	2
3	a	2010-02-21 08:19:29.000	1716	2
5	a	2010-02-21 10:13:49.000	1602	3
*/


;with cte as(
select 
(case when [status]='a' then datediff(mi,[time],getdate())
 else datediff(mi,getdate(),[time]) end) [time],
row_number() over (partition by [status] order by [time]) grp
from tb)
select grp,sum([time]) from cte group by grp
/*
1	2
2	54
3	1599
*/

select sum(case when [status]='a' then datediff(mi,[time],getdate())
 else datediff(mi,getdate(),[time]) end) [sum] from tb
/*
1655
*/

#18

select sum(datediff(ss,t.[time],isnull(b.time,getdate()))) as 时间总和 from 
(select *,row_number() over(order by id asc) as rownum from tb where status='a') t
inner join
(select *,row_number() over(order by id asc) as rownum from tb b where status='b') b
on 
t.rownum=b.rownum
select * from tb

#19

弱弱的问一下，如果多一个字段 userid，就是用于区分不同用户的，group by应该如何加上去，我自己试了一下，好像出来的分组结果不正确。

select 
  sum(datediff(mi,a.[time],isnull(b.[time],getdate()))) as [时间差总和(分钟)]
from
(select *,px=(select count(1) from tb where status='a' and [time]<t.[time]) from tb t where status='a') a
left join
(select *,px=(select count(1) from tb where status='b' and [time]<t.[time]) from tb t where status='b') b
on a.px=b.px

#20

select 
  userid,
  sum(datediff(mi,a.[time],isnull(b.[time],getdate()))) as [时间差总和(分钟)]
from
(select *,px=(select count(1) from tb where status='a' and userid=t.userid and [time]<t.[time]) from tb t where status='a') a
left join
(select *,px=(select count(1) from tb where status='b' and userid=t.userid and [time]<t.[time]) from tb t where status='b') b
on a.px=b.px and a.userid=b.userid
group by a.userid

#21

该回复于2010-02-23 16:31:37被版主删除

#22

TO ：(百年树人) ，谢谢你的解答
运算出来的结果是正确的，不过我还不太理解你的写法，例如 子句里面的 

SELECT COUNT(1) FROM missionArrange WHERE historyOperateType........发挥了什么作用,如果你能够解释一下你整个语句就好了

select 
  userid,
  sum(datediff(mi,a.[time],isnull(b.[time],getdate()))) as [时间差总和(分钟)]
from
(select *,px=(select count(1) from tb where status='a' and userid=t.userid and [time]<t.[time]) from tb t where status='a') a
left join
(select *,px=(select count(1) from tb where status='b' and userid=t.userid and [time]<t.[time]) from tb t where status='b') b
on a.px=b.px and a.userid=b.userid
group by a.userid

#23

就是按userid分组，time排序，增加一个行号而已