求两者之差

Question

----------------------------------------------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2014-01-14 14:39:38
-- Version:
--      Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) 
-- Dec 28 2012 20:23:12 
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[TB]
if object_id('[TB]') is not null drop table [TB]
go 
create table [TB]([时间] datetime,[状态] int)
insert [TB]
select '2001-01-01 10:23:00',1 union all
select '2001-01-01 10:26:00',0 union all
select '2001-01-01 10:28:00',1 union ALL
select '2001-01-01 10:33:00',0 union all
select '2001-01-01 10:43:00',1 union all
select '2001-01-01 10:53:00',0 union all
select '2001-01-01 10:59:00',1 union all
select '2001-01-01 11:23:00',1 union all
select '2001-01-01 11:40:00',0
--------------开始查询--------------------------
;WITH cte AS (
SELECT * ,ROW_NUMBER()OVER(PARTITION BY [状态] ORDER BY [时间] )id
FROM TB)
SELECT *,DATEDIFF(minute,CASE WHEN a.[状态]=0 AND b.[状态]=1 THEN b.[时间] ELSE NULL END ,CASE WHEN a.[状态]=0 AND b.[状态]=1 THEN a.[时间] ELSE NULL END )[时间差]
FROM cte a LEFT JOIN cte b ON a.id=b.id AND a.[状态]<>b.[状态]
WHERE DATEDIFF(minute,CASE WHEN a.[状态]=0 AND b.[状态]=1 THEN b.[时间] ELSE NULL END ,CASE WHEN a.[状态]=0 AND b.[状态]=1 THEN a.[时间] ELSE NULL END ) IS NOT NULL --------结果----------------------------
/* 
时间                      状态          id                   时间                      状态          id                   时间差
----------------------- ----------- -------------------- ----------------------- ----------- -------------------- -----------
2001-01-01 10:26:00.000 0           1                    2001-01-01 10:23:00.000 1           1                    3
2001-01-01 10:33:00.000 0           2                    2001-01-01 10:28:00.000 1           2                    5
2001-01-01 10:53:00.000 0           3                    2001-01-01 10:43:00.000 1           3                    10
2001-01-01 11:40:00.000 0           4                    2001-01-01 10:59:00.000 1           4                    41
*/

Answer 1

上面的不够严谨，用这个：
----------------------------------------------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2014-01-14 14:39:38
-- Version:
--      Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) 
-- Dec 28 2012 20:23:12 
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[TB]
if object_id('[TB]') is not null drop table [TB]
go 
create table [TB]([时间] datetime,[状态] int)
insert [TB]
select '2001-01-01 10:23:00',1 union all
select '2001-01-01 10:26:00',0 union all
select '2001-01-01 10:28:00',1 union ALL
select '2001-01-01 10:33:00',0 union all
select '2001-01-01 10:43:00',1 union all
select '2001-01-01 10:53:00',0 union all
select '2001-01-01 10:59:00',1 union all
select '2001-01-01 11:23:00',1 union all
select '2001-01-01 11:40:00',0
--------------开始查询--------------------------
;WITH cte AS (
SELECT * ,ROW_NUMBER()OVER(ORDER BY 时间)id
FROM TB),
cte2 AS (
SELECT DISTINCT  CASE WHEN a.状态=b.状态 THEN b.时间 ELSE a.时间 END 时间,CASE WHEN a.状态=b.状态 THEN b.状态 ELSE a.状态 END 状态
FROM cte a LEFT JOIN cte b ON a.id+1=b.id),cte3 AS (
SELECT *,ROW_NUMBER()OVER(PARTITION BY 状态 ORDER BY 时间)id
FROM cte2)
SELECT a.时间,b.时间,a.状态,b.状态,DATEDIFF(MINUTE,a.时间,b.时间)[时间差]
FROM cte3 a INNER JOIN cte3 b ON a.id=b.id AND a.状态<>b.状态
WHERE DATEDIFF(MINUTE,a.时间,b.时间)>0--------结果----------------------------
/* 
时间                      时间                      状态          状态          时间差
----------------------- ----------------------- ----------- ----------- -----------
2001-01-01 10:23:00.000 2001-01-01 10:26:00.000 1           0           3
2001-01-01 10:28:00.000 2001-01-01 10:33:00.000 1           0           5
2001-01-01 10:43:00.000 2001-01-01 10:53:00.000 1           0           10
2001-01-01 11:23:00.000 2001-01-01 11:40:00.000 1           0           17*/