select b.shipname as 船舶名称,a.mmsi as MMSI,a.callsign as 呼号,a.imo as IMO,c.name as 船舶类型, (a.loa/10) || '米' as 船长,(a.beam/10) || '米' as 船宽,(a.draught/1000) || '米' as 吃水,trunc(f.HDG/1000) || '度' as 船首向, (f.COG/100) || '度' as 船迹向, TO_CHAR(f.SOG,'FM99990.0999') || '节' as 航速,f.LAT as 纬度,f.LON as 经度, f.DEST as 目的地,to_date(extract(year from sysdate)|| f.ETA,'yyyy-MM-dd HH24:mi:ss') as 预到时间,f.lasttime as 数据更新时间 from ship_basic a left join ship_yhbasic b on a.shipid = b.mmsi left join ship_type c on a.shiptype = c.code left join ( select e.*
from (select d.*, row_number() over(partition by d.shipid order by d.creatortime desc) rw
from ship_dynamicdata d) e where e.rw = 1 ) f on a.shipid = f.shipid ORDER BY b.shipname
转载于:https://my.oschina.net/u/2446847/blog/1930556