求一条关于时间条件的语句
大家好！
我有一个表TableTime如下：
Barcode Createdate Result
KK1 2007-09-23 01:21:33 NG
KK1 2007-09-23 02:22:33 NG
KK1 2007-09-23 04:36:21 NG
KK2 2007-09-23 05:36:21 OK
KK2 2007-09-23 06:36:21 OK
KK3 2007-09-23 04:34:21 NG
KK1 2007-09-23 04:58:21 OK
KK3 2007-09-23 08:34:21 NG
........
我想找出数据库中同一条码的时间最后的记录，
如表中的结果就应该是：
KK1 2007-09-23 04:58:21 OK
KK2 2007-09-23 06:36:21 OK
KK3 2007-09-23 08:34:21 NG
请问这个语句该怎么写？
[解决办法]
select * from TableTime
where Barcode,Createdate in (
select Barcode ,max(Createdate)
from TableTime
group by Barcode )
[解决办法]
select a.*
from
tabletime a,
(select Barcode ,max(Createdate) creatdate
from TableTime
group by Barcode
) b
where
a.Barcode=b.Barcode
and a.Createdate=b.Createdate
[解决办法]
select *
from (
select tt.*,
row_number() over(partition by Barcode order by Createdate desc) as rn
from TableTime tt
)zz
where rn = 1;
-------------------------------------
如果是NG的情况:
select *
from (
select tt.*,
row_number() over(partition by Barcode order by Createdate desc) as rn
from TableTime tt
)zz
where rn = 1
and tt.Result = 'NG ';
[解决办法]
create table TableTime(Barcode varchar2(10), Createdate varchar2(50), Result varchar2(50));
insert into TableTime
select 'KK1 ', '2007-09-23 01:21:33 ', 'NG ' from dual
union all
select 'KK1 ', '2007-09-23 02:22:33 ', 'NG ' from dual
union all
select 'KK1 ', '2007-09-23 04:36:21 ', 'NG ' from dual
union all
select 'KK2 ', '2007-09-23 05:36:21 ', 'OK ' from dual
union all
select 'KK2 ', '2007-09-23 06:36:21 ', 'OK ' from dual
union all
select 'KK3 ', '2007-09-23 04:34:21 ', 'NG ' from dual
union all
select 'KK1 ', '2007-09-23 04:58:21 ', 'OK ' from dual
union all
select 'KK3 ', '2007-09-23 08:34:21 ', 'NG ' from dual;
select * from TableTime A where not exists(select 1 from TableTime T where A.Barcode=T.Barcode and A.Createdate <T.Createdate)
order by A.barcode
测试结果如下：
KK1 2007-09-23 04:58:21 OK
KK2 2007-09-23 06:36:21 OK
KK3 2007-09-23 08:34:21 NG
楼主测试下
