SELECT

t1.单号,

t1.状态 

FROM

( SELECT a.id, a.状态, a.时间, b.单号 FROM a, b WHERE a.id = b.id ) t1,

(

SELECT

b.单号,

max( a.时间 ) 时间 

FROM

a,

b 

WHERE

a.id = b.id 

GROUP BY

b.单号 

) t2 

WHERE

t1.单号 = t2.单号 

AND t1.时间 = t2.时间

先将A表按ID、MAX(FDate) AS MaxFDate分组,B表通过ID和MaxFDate连接A表，得到状态。

---

Oracle：

select * from (select a.*,row_number()over(partition by a.单号 order by a.时间 desc)fn  from a) where fn=1