22,206
社区成员
发帖
与我相关
我的任务
分享
--> 测试数据:[test]
if object_id('[test]') is not null
drop table [test]
create table [test](
[rfid] varchar(12),
[time] datetime,
[code] varchar(7),
[run_code] int
)
insert [test]
select 'RF12-0000018','2012-04-29 09:41:36','RM4-R01',2 union all
select 'RF12-0000018','2012-04-29 10:39:14','RM4-R01',2 union all
select 'RF12-0000018','2012-04-29 10:53:05','RM4-R01',3 union all
select 'RF12-0000018','2012-04-29 13:24:59','RM4-R01',2 union all
select 'RF12-0000018','2012-04-29 13:43:58','RM4-R01',2 union all
select 'RF12-0000018','2012-04-29 15:33:47','RM4-R01',2 union all
select 'RF12-0000018','2012-04-29 15:52:49','RM4-R01',3
with t
as(
select ROW_NUMBER()OVER(order by getdate()) as px,* from test
)
select [rfid],[time],[code],[run_code] from t a
where exists (select 1 from(
select * from t a where [run_code]=3)b where a.px=b.px or a.px=b.px-1)
/*
rfid time code run_code
RF12-0000018 2012-04-29 10:39:14.000 RM4-R01 2
RF12-0000018 2012-04-29 10:53:05.000 RM4-R01 3
RF12-0000018 2012-04-29 15:33:47.000 RM4-R01 2
RF12-0000018 2012-04-29 15:52:49.000 RM4-R01 3
*/