求一sql语句，最好执行效率高一些
有一个表
ID deviceID statue createdate
1 1002 1 2013-12-20 01:00:52
2 1003 0 2013-12-20 01:00:55
3 1004 1 2013-12-20 01:01:42
4 1002 0 2013-12-20 01:15:53
5 1003 1 2013-12-20 01:15:57
6 1004 1 2013-12-20 01:16:42
7 1002 1 2013-12-20 01:30:53
8 1003 0 2013-12-20 01:30:57
9 1004 1 2013-12-20 01:31:43
上面的表记录了设备的运行状态，每隔15分钟写入一次，设备大约有1000个， 我要想要写一个存储过程，能够查询设备最后一次运行状态为0 ，但上一次运行状态为1的所有的设备列表
如上面表中 只需要查询出
8 1003 0 2013-12-20 01:30:57
谢谢
[解决办法]
建表：
create table a(ID int, deviceID int, statue int, createdate datetime)
insert into a
select 1 ,1002 ,1 ,'2013-12-20 01:00:52' union all
select 2 ,1003 ,0 ,'2013-12-20 01:00:55' union all
select 3 ,1004 ,1 ,'2013-12-20 01:01:42' union all
select 4 ,1002 ,0 ,'2013-12-20 01:15:53' union all
select 5 ,1003 ,1 ,'2013-12-20 01:15:57' union all
select 6 ,1004 ,1 ,'2013-12-20 01:16:42' union all
select 7 ,1002 , 1 ,'2013-12-20 01:30:53' union all
select 8 ,1003 , 0 ,'2013-12-20 01:30:57' union all
select 9 ,1004 , 1 ,'2013-12-20 01:31:43'
go
if OBJECT_ID('tempdb..#temp') is not null
drop table #temp
select deviceID,MAX(id) id into #temp
from a
group by deviceID
select a.*
from a
inner join #temp t
on t.deviceID = a.deviceID and
t.id = a.ID and
a.statue = 0
inner join a aa
on t.deviceID = aa.deviceID and
t.id > aa.ID and
aa.statue = 1
/*
IDdeviceIDstatuecreatedate
8100302013-12-20 01:30:57.000
*/
WITH a1 (ID,deviceID,statue,createdate) AS
(
SELECT 1, 1002, 1,'2013-12-20 01:00:52' UNION ALL
SELECT 2, 1003, 0,'2013-12-20 01:00:55' UNION ALL
SELECT 3, 1004, 1,'2013-12-20 01:01:42' UNION ALL
SELECT 4, 1002, 0,'2013-12-20 01:15:53' UNION ALL
SELECT 5, 1003, 1,'2013-12-20 01:15:57' UNION ALL
SELECT 6, 1004, 1,'2013-12-20 01:16:42' UNION ALL
SELECT 7, 1002, 1,'2013-12-20 01:30:53' UNION ALL
SELECT 8, 1003, 0,'2013-12-20 01:30:57' UNION ALL
SELECT 9, 1004, 1,'2013-12-20 01:31:43'
)
SELECT a.*
FROM a1 a
CROSS APPLY
(
SELECT MAX(id) id FROM a1 WHERE deviceID=a.deviceID
) b
CROSS APPLY
(
SELECT TOP 1 statue FROM a1 WHERE deviceID=a.deviceID AND id<b.id ORDER BY id desc
) c
WHERE a.id=b.id AND a.statue=0 AND c.statue=1
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-12-20 12:35:25
-- 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: )
--
----------------------------------------------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([ID] int,[deviceID] int,[statue] int,[createdate] datetime)
insert [huang]
select 1,1002,1,'2013-12-20 01:00:52' union all
select 2,1003,0,'2013-12-20 01:00:55' union all
select 3,1004,1,'2013-12-20 01:01:42' union all
select 4,1002,0,'2013-12-20 01:15:53' union all
select 5,1003,1,'2013-12-20 01:15:57' union all
select 6,1004,1,'2013-12-20 01:16:42' union all
select 7,1002,1,'2013-12-20 01:30:53' union all
select 8,1003,0,'2013-12-20 01:30:57' union all
select 9,1004,1,'2013-12-20 01:31:43'
--------------开始查询--------------------------
;WITH cte AS (select *,ROW_NUMBER()OVER(PARTITION BY [deviceID] ORDER BY [createdate])rn
from [huang] )
SELECT TOP 1 [ID],[deviceID],[statue],[createdate]
FROM cte
WHERE [statue]=0 AND
EXISTS (SELECT 1 FROM cte b WHERE cte.deviceid=b.deviceid AND cte.statue=0 AND b.[statue]=1 AND cte.rn=b.rn+1 AND cte.createdate>b.createdate)
ORDER BY createdate DESC
----------------结果----------------------------
/*
ID deviceID statue createdate
----------- ----------- ----------- -----------------------
8 1003 0 2013-12-20 01:30:57.000
*/
