Table : A ; Column: REEL_ID,REEL_BARCODE
Table : B ; Column: REEL_ID,TIMESTAMP
Table B中有多组重复的Reel_ID数据,通过A关联B,找到B中最大的TIMESTAMP的一行数据。
语句1如下:
SELECT * FROM (
SELECT A.REEL_ID,A.REEL_BARCODE,B.FEEDER_ID,B.TIMESTAMP, ROW_NUMBER() OVER(PARTITION BY A.reel_id ORDER BY B.TIMESTAMP DESC) RNO FROM reel_data A JOIN feeder_reel_history B
ON A.reel_id = B.reel_id and A.REEL_BARCODE in ('AADSSSSSSS'))
C WHERE C.RNO = 1;
其中RNO,代表数据第几行.
语句2如下:
select A.REEL_ID,A.REEL_BARCODE,B.FEEDER_ID,B.TIMESTAMP
from REEL_DATA A join feeder_reel_history B on A.REEL_ID = B.REEL_ID
and A.REEL_BARCODE in ('AADSSSSSSS')
and B.TIMESTAMP in (select max(C.TIMESTAMP) from feeder_reel_history C where C.REEL_ID = A.REEL_ID group by C.REEL_ID)