select 'PBN运行航路' 类型, fb.b 总条数, fa.a 总距离 from

                (

                select sum(s)  a  from (

                            select distinct f4.airway_point1,f4.airway_point2, f4.kilometer_distance S  from airway f1, airway_segment_point f2,airway_segment_point f3, segment f4

                            where

                            f1.chart_type_id=3 and

                            f1.airway_id =f2.airway_id and

                            f1.airway_id =f3.airway_id and

                            f2.airway_seq=f3.airway_seq-1 and

                            (

                            (f2.airway_point_id =f4.airway_point1 and f3.airway_point_id =f4.airway_point2) or

                            (f2.airway_point_id =f4.airway_point2 and f3.airway_point_id =f4.airway_point1)

                            ) and f1.airway_rnp is not null

                            )  tm

                )  fa

                left join

                (select  count(*) b from airway where airway_rnp is not null and chart_type_id=3 )   fb

                on 1=1

 

纯PBN代号航路

select 'PBN代号航路' 类型, fb.b 总条数, fa.a 总距离 from

                (

                select sum(s)  a  from (

                            select distinct f4.airway_point1,f4.airway_point2, f4.kilometer_distance S  from airway f1, airway_segment_point f2,airway_segment_point f3, segment f4

                            where

                            f1.chart_type_id=3 and

                            f1.airway_id =f2.airway_id and

                            f1.airway_id =f3.airway_id and

                            f2.airway_seq=f3.airway_seq-1 and

                            (

                            (f2.airway_point_id =f4.airway_point1 and f3.airway_point_id =f4.airway_point2) or

                            (f2.airway_point_id =f4.airway_point2 and f3.airway_point_id =f4.airway_point1)

                            ) and f1.airway_type_id in (2,5,8)

                            )  tm

                )  fa

                left join

                (select  count(*) b from airway where airway_type_id in (2,5,8) and chart_type_id=3 )   fb

                on 1=1