好久没写SQL，请各位C友帮忙看看!解决方案
日期：2014-05-18 浏览次数：20473 次
好久没写SQL，请各位C友帮忙看看!
------解决方案--------------------
鹤兄，好久不见了。
------解决方案--------------------
看懂了。。。但是感觉无从下手 囧
------解决方案--------------------
- SQL code
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
------解决方案--------------------
鹤兄，好久不见了。
------解决方案--------------------
看懂了。。。但是感觉无从下手 囧
------解决方案--------------------
- SQL code
;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 行受影响)
**/
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
