有一表格数据如下：
rfid time code run_code
RF12-0000018 2012-04-29 09:41:36 RM4-R01 2
RF12-0000018 2012-04-29 10:39:14 RM4-R01 2
RF12-0000018 2012-04-29 10:53:05 RM4-R01 3
RF12-0000018 2012-04-29 13:24:59 RM4-R01 2
RF12-0000018 2012-04-29 13:43:58 RM4-R01 2
RF12-0000018 2012-04-29 15:33:47 RM4-R01 2
RF12-0000018 2012-04-29 15:52:49 RM4-R01 3
现在想用一条sql语句查找出来的结果如下所示：
rfid time code run_code
RF12-0000018 2012-04-29 10:39:14 RM4-R01 2
RF12-0000018 2012-04-29 10:53:05 RM4-R01 3
RF12-0000018 2012-04-29 15:33:47 RM4-R01 2
RF12-0000018 2012-04-29 15:52:49 RM4-R01 3
也就是run_code结果为2、3；2、3；当有重复的2出现时只取3前面的最近的2。
不知道大家明白没，求帮忙！！！
------解决方案--------------------
- SQL code
--> 测试数据：[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 allselect 'RF12-0000018','2012-04-29 10:39:14','RM4-R01',2 union allselect 'RF12-0000018','2012-04-29 10:53:05','RM4-R01',3 union allselect 'RF12-0000018','2012-04-29 13:24:59','RM4-R01',2 union allselect 'RF12-0000018','2012-04-29 13:43:58','RM4-R01',2 union allselect 'RF12-0000018','2012-04-29 15:33:47','RM4-R01',2 union allselect 'RF12-0000018','2012-04-29 15:52:49','RM4-R01',3with t as(select ROW_NUMBER()OVER(order by getdate()) as px,* from test)select [rfid],[time],[code],[run_code] from t awhere 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_codeRF12-0000018 2012-04-29 10:39:14.000 RM4-R01 2RF12-0000018 2012-04-29 10:53:05.000 RM4-R01 3RF12-0000018 2012-04-29 15:33:47.000 RM4-R01 2RF12-0000018 2012-04-29 15:52:49.000 RM4-R01 3*/