好久没写SQL，请各位C友帮忙看看!
CREATE TABLE #([type] varchar(10),[date] datetime,value int)INSERT INTO #SELECT 'fault','2010-01-02 09:01:00',null UNION ALLSELECT 'stop','2010-01-02 09:02:00',null UNION ALLSELECT 'running','2010-01-02 09:03:00',null UNION ALLSELECT 'fault','2010-01-02 09:04:00',null UNION ALLSELECT 'fault','2010-01-02 09:04:00',null UNION ALLSELECT 'stop','2010-01-02 09:05:00',null UNION ALLSELECT 'block','2010-01-02 09:06:00',null UNION ALLSELECT 'running','2010-01-02 09:06:00',null UNION ALLSELECT 'fault','2010-01-02 09:08:00',null UNION ALLSELECT '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 2stop 2010-01-02 09:02:00.000 2010-01-02 09:03:00.000 1 2running 2010-01-02 09:03:00.000 2010-01-02 09:04:00.000 1 1fault 2010-01-02 09:04:00.000 2010-01-02 09:05:00.000 1 4fault 2010-01-02 09:04:00.000 2010-01-02 09:05:00.000 1 4stop 2010-01-02 09:05:00.000 2010-01-02 09:06:00.000 1 4block 2010-01-02 09:06:00.000 2010-01-02 09:08:00.000 2 4running 2010-01-02 09:06:00.000 2010-01-02 09:08:00.000 2 2fault 2010-01-02 09:08:00.000 2010-01-02 09:11:00.000 3 3fault 2010-01-02 09:11:00.000 NULL NULL 3
;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 #))endfrom # 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 torder by date/**type date next_date value value1---------- ----------------------- ----------------------- ----------- -----------fault 2010-01-02 09:01:00.000 2010-01-02 09:02:00.000 1 2stop 2010-01-02 09:02:00.000 2010-01-02 09:03:00.000 1 2running 2010-01-02 09:03:00.000 2010-01-02 09:04:00.000 1 1fault 2010-01-02 09:04:00.000 2010-01-02 09:05:00.000 1 2fault 2010-01-02 09:04:00.000 2010-01-02 09:05:00.000 1 2stop 2010-01-02 09:05:00.000 2010-01-02 09:06:00.000 1 2block 2010-01-02 09:06:00.000 2010-01-02 09:08:00.000 2 2running 2010-01-02 09:06:00.000 2010-01-02 09:08:00.000 2 2fault 2010-01-02 09:08:00.000 2010-01-02 09:11:00.000 3 3fault 2010-01-02 09:11:00.000 NULL NULL 3(10 行受影响)**/
