delete a from TABLE right join (Select tubeno,max(date + time) as dt from table group by tubeno) b on a.tubeno = b.tubeno and a.date + a.time = b.dt where a.pdl is null
表中有自增长字段,完整的表如下所示: AUTNO LOT TUBENO OPERATOR TESTDATE TESTTIME ITEM VL 7 03105-0603001 030089084 010654 2003-6-12 10:09:08AM IL 0.269 8 03105-0603001 030089085 010654 2003-6-12 10:12:08AM IL 0.278 9 03105-0603001 030089086 010654 2003-6-12 10:15:08AM IL 0.277 10 03105-0603001 030089086 010654 2003-6-12 10:17:08AM IL 12.975 11 03105-0603001 030089086 010654 2003-6-12 11:08:08AM IL 12.96 12 03105-0603001 030089087 010654 2003-6-12 5:09:08PM IL 12.935 13 03105-0603001 030089088 010654 2003-6-11 6:09:08PM IL 0.042 14 03105-0603001 030089088 010654 2003-6-16 10:09:08AM IL 0.05 15 03105-0603001 030089089 010654 2003-6-12 7:09:08 PM IL 0.048用SQL查询要过滤“TUBENO”重复部份,且只留最后时间之资料,如上表查询后得下面资料: AUTNO LOT TUBENO OPERATOR TESTDATE TESTTIME ITEM VL 7 03105-0603001 030089084 010654 2003-6-12 10:09:08AM IL 0.269 8 03105-0603001 030089085 010654 2003-6-12 10:12:08AM IL 0.278 11 03105-0603001 030089086 010654 2003-6-12 11:08:08AM IL 12.96 12 03105-0603001 030089087 010654 2003-6-12 5:09:08PM IL 12.935 14 03105-0603001 030089088 010654 2003-6-16 10:09:08AM IL 0.05 15 03105-0603001 030089089 010654 2003-6-12 7:09:08 PM IL 0.048SQL语句怎么写?
select a.* from ttmp a, (select tubeno,max(testtime) testtime from ttmp group by tubeno) b where a.tubeno=b.tubeno and a.testtime=b.testtime
right join (Select tubeno,max(date + time) as dt from table group by tubeno) b
on a.tubeno = b.tubeno and a.date + a.time = b.dt
where a.pdl is null
AUTNO LOT TUBENO OPERATOR TESTDATE TESTTIME ITEM VL
7 03105-0603001 030089084 010654 2003-6-12 10:09:08AM IL 0.269
8 03105-0603001 030089085 010654 2003-6-12 10:12:08AM IL 0.278
9 03105-0603001 030089086 010654 2003-6-12 10:15:08AM IL 0.277
10 03105-0603001 030089086 010654 2003-6-12 10:17:08AM IL 12.975
11 03105-0603001 030089086 010654 2003-6-12 11:08:08AM IL 12.96
12 03105-0603001 030089087 010654 2003-6-12 5:09:08PM IL 12.935
13 03105-0603001 030089088 010654 2003-6-11 6:09:08PM IL 0.042
14 03105-0603001 030089088 010654 2003-6-16 10:09:08AM IL 0.05
15 03105-0603001 030089089 010654 2003-6-12 7:09:08 PM IL 0.048用SQL查询要过滤“TUBENO”重复部份,且只留最后时间之资料,如上表查询后得下面资料:
AUTNO LOT TUBENO OPERATOR TESTDATE TESTTIME ITEM VL
7 03105-0603001 030089084 010654 2003-6-12 10:09:08AM IL 0.269
8 03105-0603001 030089085 010654 2003-6-12 10:12:08AM IL 0.278
11 03105-0603001 030089086 010654 2003-6-12 11:08:08AM IL 12.96
12 03105-0603001 030089087 010654 2003-6-12 5:09:08PM IL 12.935
14 03105-0603001 030089088 010654 2003-6-16 10:09:08AM IL 0.05
15 03105-0603001 030089089 010654 2003-6-12 7:09:08 PM IL 0.048SQL语句怎么写?
select a.* from ttmp a, (select tubeno,max(testtime) testtime from ttmp group by tubeno) b where a.tubeno=b.tubeno and a.testtime=b.testtime