select * from
(select min(substr(a.txt_name_admin,1,2)) 地区, min(a.txt_name) 机场名称, min(p.code_icao) 机场四码, '进场' 程序类型, min(p.txt_desig) 程序,min(p.code_trans_id) 对应跑道,
sum(to_number(substr(g.leg_distance,instr(g.leg_distance,'/')+1))) 公里距离
from STAR p, procedure_leg g,ad_hp a
where p.star_id=g.star_id
and p.code_icao =a.code_icao
and p.star_id in(select min(star_id) from star group by code_icao,txt_desig)
group by p.star_id
union
select min(substr(a.txt_name_admin,1,2)) 地区, min(a.txt_name) 机场名称, min(p.code_icao) 机场四码, '离场' 程序类型, min(p.txt_desig) 程序,min(p.code_trans_id) 对应跑道,
sum(to_number(substr(g.leg_distance,instr(g.leg_distance,'/')+1))) 公里距离
from sid p, procedure_leg g,ad_hp a
where p.sid_id=g.sid_id
and p.code_icao =a.code_icao
and p.sid_id in(select min(sid_id) from sid group by code_icao,txt_desig)
group by p.sid_id
union
select min(substr(a.txt_name_admin,1,2)) 地区, min(a.txt_name) 机场名称, min(p.code_icao) 机场四码, '进近' 程序类型, min(p.txt_desig) 程序,min(p.code_trans_id) 对应跑道,
sum(to_number(substr(g.leg_distance,instr(g.leg_distance,'/')+1))) 公里距离
from iap p, procedure_leg g,ad_hp a
where p.iap_id=g.iap_id
and p.code_icao =a.code_icao
and p.iap_id in(select min(iap_id) from iap group by code_icao,txt_desig)
group by p.iap_id
)
where 公里距离 is not null
order by 地区,机场名称,decode(程序类型,'离场',1,'进场',2,'进近',3)