select sum(t2-t1) as totaltime,max(t2-t1) as longesttime from
(select t2=(select min(time) from [table] where state=1 and time>a.time),t1 from [table] where state=0) a
(select t2=(select min(time) from [table] where state=1 and time>a.time),t1 from [table] where state=0) a
我将你的意思转化为如下,但有语法错误:
select t2-t1,max(t2-t1)
from
(select
t2=
(select top 1 min(TopDate) from XingShiLiCheng
where TopDate>a.TopDate and state='熄火')
,t1=topDate
from XingShiLiCheng
where State='点火') a
Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'a' does not match with a table name or alias name used in the query.
select id=identity(1,1),*
into #t
from tablename
order by timesselect 总时间=sum(datediff(minute,t0,t)),
最长时间=max(datediff(minute,t0,t))
from ( select times as t, (select times from #t where id=a.id-1) as t0
from #t a
where [state]=1 and ( select [state] from #t where id=a.id)=0
) bdrop table #t
zheninchangjiang(我爱燕子)
我的解决方法如下:调试通过。但在0,1不配对的情况好像有点问题。select sum(datediff(min,t2,t1)) ,max(datediff(min,t2,t1))
from
(
select t2=(select min(time) from [table] where state=1 and time>a.time),t1=time from (select time from table where state=0) a
)
go
insert into xtable select
getdate(),0 union all select
dateadd(minute,10,getdate()),1 union all select
dateadd(minute,20,getdate()),0 union all select
dateadd(minute,30,getdate()),0 union all select
dateadd(minute,40,getdate()),1 union all select
dateadd(minute,50,getdate()),1 union all select
dateadd(minute,60,getdate()),0 union all select
dateadd(minute,70,getdate()),1 union all select
dateadd(minute,80,getdate()),0 union all select
dateadd(minute,90,getdate()),1 union all select
dateadd(minute,100,getdate()),0 union all select
dateadd(minute,101,getdate()),1
select sum(datediff(minute,t1,t2)) ,max(datediff(minute,t1,t2))
from
(
select t2=(select min(times) from [xtable] b where b.state=1 and
b.times>a.times and not exists
(select 1 from [xtable] where times>a.times and times<b.times) ),
t1=times from xtable a where state=0) a
/*
----------- -----------
41 10(所影响的行数为 1 行)警告: 聚合或其它 SET 操作消除了空值。
*/