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;