sql

select '{

        "totalAmount": '|| fo.total_price ||',

        "orderType": 7,

        "currentCompanyId": "'|| company_id ||'",

        "currentEmployeeId": "'|| fo.employee_id ||'",

        "orderList": [

        {

        "operatorType": 1,

        "reason": "出票失败，退款",

        "supplierCompanyId": "'|| max(a.supplier_id) ||'",

        "amount": '|| fo.total_price ||',

        "callbackCheck": 2,

        "orderId": "'|| fo.id ||'",

        "commonJson": {

        "companyInfo": {

        "companyName": "'|| fo.company_name ||'",

        "comment": "出票失败",

        "operatorName": "'|| fo.employee_name ||'"

        },

        "air": {

        "endCityName": "'|| max(a.destination_city) ||'",

        "passagerList": [' || string_agg('{

        "passagerPhone": "' ||(a.passenger_info :: json ->> 'phone') ||'",

        "passagerName": "'|| (a.passenger_info :: json ->> 'name') ||'"

        }', ',') ||'

        ],

        "flightNo": "' || max(a.segment_info :: json ->> 'flight_no') || '",

        "airlineName": "' || max(a.segment_info :: json ->> 'airline_name') ||'"

        }

        },

        "comment": "出票失败，退款",

        "operatorId": "'|| fo.employee_id ||'",

        "operatorName": "'|| fo.employee_name ||'",

        "purchaseAmount": '|| fo.cost_price ||'

        }

        ],

        "businessType": 3

        }'

from fb_order fo

       left join fb_order_product fop on fo.id = fop.fb_order_id

       left join air_ticket a on a.id = fop.product_id

where fo.id in (select distinct af.order_id

                from account_flow af

                       left join (select order_id, count(order_id) as t_count

                                  from account_flow

                                  where order_id in (select order_id

                                                     from (select fo.id as order_id, count(fo.id) as count

                                                           from fb_order fo

                                                                  inner join fb_order_product fop

                                                                    on fo.id = fop.fb_order_id

                                                                  left join air_ticket a on a.id = fop.product_id

                                                           where fo.status = 1801

                                                             and fo.is_track_issued = true

                                                             and fo.create_time > '2018-09-01 00:00:00'

                                                             and fo.create_time <= now()

                                                           group by fo.id) as table_1

                                                     where count > 1)

                                  group by order_id) as table_2 on af.order_id = table_2.order_id

                where table_2.t_count = 2)

group by fo.id;

 

 

select fo.id, max(a.supplier_id)

from fb_order fo

       left join fb_order_product fop on fo.id = fop.fb_order_id

       left join air_ticket a on a.id = fop.product_id

where fo.id = '5bb8ba3be4b0543600861d5f'

group by fo.id;