困惑好久的问题

Question

时间                               状态
2011-07-07 17:49:04.0000000  1
2011-07-07 17:54:04.0000000          0
2011-07-07 17:54:11.0000000          1
2011-07-07 17:54:15.0000000  1
2011-07-07 17:54:24.0000000          0
2011-07-07 17:54:31.0000000          1
2011-07-07 17:54:38.0000000          0
2011-07-07 17:54:45.0000000          0
2011-07-07 17:54:52.0000000          1
2011-07-07 18:11:58.0000000          0
2011-07-07 18:12:00.0000000          1
2011-07-07 18:12:02.0000000          0
2011-07-07 18:12:07.0000000          0如果连续出现多个1或者0，取出现的第一个
即处理成时间                           状态
2011-07-07 17:49:04.0000000      1
2011-07-07 17:54:04.0000000      0
2011-07-07 17:54:11.0000000      1
2011-07-07 17:54:24.0000000      0
2011-07-07 17:54:31.0000000      1
2011-07-07 17:54:38.0000000      0
2011-07-07 17:54:52.0000000      1
2011-07-07 18:11:58.0000000     0
2011-07-07 18:12:00.0000000     1
2011-07-07 18:12:02.0000000     0查询出前一个状态的持续时间
比如
状态1的持续时间就是2011-07-07 17:54:04.0000000 和 2011-07-07 17:49:04.0000000的差值
状态0的持续时间就是2011-07-07 17:54:11.0000000和 2011-07-07 17:54:04.0000000的差值
最终处理成  
持续时间   （秒）        状态
300                  1
7                  0
13                  1
7                  0
..                  1
..                  0
..                  1
..                  0
..                  1
..                  0

Answer 1

declare @T table (col datetime,state varchar(16),c3 int)
insert into @T
select '2011-07-07','17:49:04.0000000',1 union all
select '2011-07-07','17:54:04.0000000',0 union all
select '2011-07-07','17:54:11.0000000',1 union all
select '2011-07-07','17:54:15.0000000',1 union all
select '2011-07-07','17:54:24.0000000',0 union all
select '2011-07-07','17:54:31.0000000',1 union all
select '2011-07-07','17:54:38.0000000',0 union all
select '2011-07-07','17:54:45.0000000',0 union all
select '2011-07-07','17:54:52.0000000',1 union all
select '2011-07-07','18:11:58.0000000',0 union all
select '2011-07-07','18:12:00.0000000',1 union all
select '2011-07-07','18:12:02.0000000',0 union all
select '2011-07-07','18:12:07.0000000',0;with maco as(
select row_number() over (order by col,state) as rid,* from @T)select convert(varchar(10),a.col,120) as col,a.state,a.c3
from maco a left join maco b on a.rid=b.rid-1
where a.c3<>isnull(b.c3,2)
/*
col        state            c3
---------- ---------------- -----------
2011-07-07 17:49:04.0000000 1
2011-07-07 17:54:04.0000000 0
2011-07-07 17:54:15.0000000 1
2011-07-07 17:54:24.0000000 0
2011-07-07 17:54:31.0000000 1
2011-07-07 17:54:45.0000000 0
2011-07-07 17:54:52.0000000 1
2011-07-07 18:11:58.0000000 0
2011-07-07 18:12:00.0000000 1
2011-07-07 18:12:07.0000000 0
*/

Answer 2

declare @T table (col datetime,state varchar(16),c3 int)
insert into @T
select '2011-07-07','17:49:04',1 union all
select '2011-07-07','17:54:04',0 union all
select '2011-07-07','17:54:11',1 union all
select '2011-07-07','17:54:15',1 union all
select '2011-07-07','17:54:24',0 union all
select '2011-07-07','17:54:31',1 union all
select '2011-07-07','17:54:38',0 union all
select '2011-07-07','17:54:45',0 union all
select '2011-07-07','17:54:52',1 union all
select '2011-07-07','18:11:58',0 union all
select '2011-07-07','18:12:00',1 union all
select '2011-07-07','18:12:02',0 union all
select '2011-07-07','18:12:07',0;with maco as(
select row_number() over (order by col,state) as rid,* from @T)
,maco1 as(
select row_number() over (order by a.col,a.state) as rid,
convert(varchar(10),a.col,120) as col,a.state,a.c3
from maco a left join maco b on a.rid=b.rid-1
where a.c3<>isnull(b.c3,2))select '持续时间（秒）'=datediff(s,a.state,b.state),状态=a.c3 
from maco1 a left join maco1 b on a.rid=b.rid-1
/*
持续时间（秒）     状态
----------- -----------
300         1
11          0
9           1
7           0
14          1
7           0
1026        1
2           0
7           1
NULL        0
*/

Answer 3

USE SSISTest;
GO
SET NOCOUNT ON
IF OBJECT_ID(N'A',N'U')IS NOT NULL DROP TABLE A
GOCREATE TABLE A--创建测试数据表
(
时间 nvarchar(30), 
状态 int)
INSERT INTO A--插入测试数据
select '2011-07-07 17:49:04.0000000' , 1 union all
select '2011-07-07 17:54:04.0000000' , 0 union all
select '2011-07-07 17:54:11.0000000' , 1 union all
select '2011-07-07 17:54:15.0000000' , 1 union all
select '2011-07-07 17:54:24.0000000' , 0 union all
select '2011-07-07 17:54:31.0000000' , 1 union all
select '2011-07-07 17:54:38.0000000' , 0 union all
select '2011-07-07 17:54:45.0000000' , 0 union all
select '2011-07-07 17:54:52.0000000' , 1 union all
select '2011-07-07 18:11:58.0000000' , 0 union all
select '2011-07-07 18:12:00.0000000' , 1 union all
select '2011-07-07 18:12:02.0000000' , 0 union all
select '2011-07-07 18:12:07.0000000' , 0
gowith cte as
(
select top 100 percent *,row_number()over(order by getdate()) as RN  from A as a
where not exists
(
  select * from (select top 1 * from A as c  where c.[时间]<a.[时间] order by c.[时间] desc) as b where   a.[状态]=b.[状态]
)
order by [时间]
)
select 
datediff(second,a.[时间],(select top 1 [时间] from cte as b where b.[时间]>a.[时间]))as[持续时间(秒)],
[状态]
from cte as ago
/*
持续时间(秒)     状态
----------- -----------
300         1
7           0
13          1
7           0
7           1
14          0
1026        1
2           0
2           1
NULL        0*/