34,594
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE #([type] varchar(10),[date] datetime,value int)
INSERT INTO #
SELECT 'fault','2010-01-02 09:01:00',null UNION ALL
SELECT 'stop','2010-01-02 09:02:00',null UNION ALL
SELECT 'running','2010-01-02 09:03:00',null UNION ALL
SELECT 'fault','2010-01-02 09:04:00',null UNION ALL
SELECT 'fault','2010-01-02 09:04:00',null UNION ALL
SELECT 'stop','2010-01-02 09:05:00',null UNION ALL
SELECT 'block','2010-01-02 09:06:00',null UNION ALL
SELECT 'running','2010-01-02 09:06:00',null UNION ALL
SELECT 'fault','2010-01-02 09:08:00',null UNION ALL
SELECT 'fault','2010-01-02 09:11:00',null
我想得到以下结果
type date next_date value value1
---------- ------------------------------------------------------ ----------- ----------- ----------- ------------
fault 2010-01-02 09:01:00.000 2010-01-02 09:02:00.000 1 2
stop 2010-01-02 09:02:00.000 2010-01-02 09:03:00.000 1 2
running 2010-01-02 09:03:00.000 2010-01-02 09:04:00.000 1 1
fault 2010-01-02 09:04:00.000 2010-01-02 09:05:00.000 1 4
fault 2010-01-02 09:04:00.000 2010-01-02 09:05:00.000 1 4
stop 2010-01-02 09:05:00.000 2010-01-02 09:06:00.000 1 4
block 2010-01-02 09:06:00.000 2010-01-02 09:08:00.000 2 4
running 2010-01-02 09:06:00.000 2010-01-02 09:08:00.000 2 2
fault 2010-01-02 09:08:00.000 2010-01-02 09:11:00.000 3 3
fault 2010-01-02 09:11:00.000 NULL NULL 3
;with t1 as(
select rn=row_number() over(order by date),type,date,
next_date=(select top 1 date from # where date>t.date order by date),
value=datediff(mi,date,(select top 1 date from # where date>t.date order by date)),
date1=
case when type='running' then
(select top 1 date from # where date>t.date and type!='running' order by date)
else
isnull((select top 1 date from # where date>=t.date and type='running' order by date),(select max(date) from #))
end
from # t
) ,
t2 as(
select type,date,next_date,value,date1,
value0=case when exists(select 1 from t1 where date1=t.date1 and date=t.date and rn<t.rn) then 0 else value end
from t1 t
)
select type,date,next_date,value,
value1=(select sum(value0) from t2 where date1=t.date1) from t2 t
/**
type date next_date value value1
---------- ----------------------- ----------------------- ----------- -----------
fault 2010-01-02 09:01:00.000 2010-01-02 09:02:00.000 1 2
stop 2010-01-02 09:02:00.000 2010-01-02 09:03:00.000 1 2
running 2010-01-02 09:03:00.000 2010-01-02 09:04:00.000 1 1
fault 2010-01-02 09:04:00.000 2010-01-02 09:05:00.000 1 4
fault 2010-01-02 09:04:00.000 2010-01-02 09:05:00.000 1 4
stop 2010-01-02 09:05:00.000 2010-01-02 09:06:00.000 1 4
block 2010-01-02 09:06:00.000 2010-01-02 09:08:00.000 2 4
running 2010-01-02 09:06:00.000 2010-01-02 09:08:00.000 2 2
fault 2010-01-02 09:08:00.000 2010-01-02 09:11:00.000 3 3
fault 2010-01-02 09:11:00.000 NULL NULL 3
(10 行受影响)
**/
fault 2010-01-02 09:04:00.000 2010-01-02 09:05:00.000 1 2
fault 2010-01-02 09:04:00.000 2010-01-02 09:05:00.000 1 2
stop 2010-01-02 09:05:00.000 2010-01-02 09:06:00.000 1 2
block 2010-01-02 09:06:00.000 2010-01-02 09:08:00.000 2 2
前两个是重复的,只能算 1 。所以 1+1+2=4.是这样来的
;with t1 as(
select type,date,
next_date=(select top 1 date from # where date>t.date order by date),
value=datediff(mi,date,(select top 1 date from # where date>t.date order by date)),
date1=
case when type='running' then
(select top 1 date from # where date>t.date and type!='running' order by date)
else
isnull((select top 1 date from # where date>=t.date and type='running' order by date),(select max(date) from #))
end
from # t
)
select type,date,next_date,value,value1=datediff(mi,(select top 1 date from t1 where date1=t.date1 order by date),date1)
from t1 t
order by date
/**
type date next_date value value1
---------- ----------------------- ----------------------- ----------- -----------
fault 2010-01-02 09:01:00.000 2010-01-02 09:02:00.000 1 2
stop 2010-01-02 09:02:00.000 2010-01-02 09:03:00.000 1 2
running 2010-01-02 09:03:00.000 2010-01-02 09:04:00.000 1 1
fault 2010-01-02 09:04:00.000 2010-01-02 09:05:00.000 1 2
fault 2010-01-02 09:04:00.000 2010-01-02 09:05:00.000 1 2
stop 2010-01-02 09:05:00.000 2010-01-02 09:06:00.000 1 2
block 2010-01-02 09:06:00.000 2010-01-02 09:08:00.000 2 2
running 2010-01-02 09:06:00.000 2010-01-02 09:08:00.000 2 2
fault 2010-01-02 09:08:00.000 2010-01-02 09:11:00.000 3 3
fault 2010-01-02 09:11:00.000 NULL NULL 3
(10 行受影响)
**/
fault 2010-01-02 09:04:00.000 2010-01-02 09:05:00.000 1 4
fault 2010-01-02 09:04:00.000 2010-01-02 09:05:00.000 1 4