select cr.profitcenter_group_code ,cr.profitcenter_name ,
v1.* from core_company cr
left join(select x.apply_company_code,x.apply_company_name,
sum(x.apply_sum_money) amount_money,count(*) amount_total,
sum(case when x.apply_bill_way='BW001' then 1 else 0 end ) predict_total,
sum(case when x.apply_bill_way='BW001' then x.apply_sum_money else 0 end ) predict_money,
sum(case when x.apply_bill_way='BW001' then 1 else 0 end ) associated_total,
sum(case when x.apply_bill_way='BW001' then x.apply_sum_money else 0 end ) associated_money,
sum(case when x.apply_bill_way='BW001' and sysdate-x.apply_date>90 then 1 else 0 end ) associated_three_total,
sum(case when x.apply_bill_way='BW001' and sysdate-x.apply_date>90 then x.apply_sum_money else 0 end ) associated_three_money,
sum(case when x.apply_bill_way='BW001' then 1 else 0 end ) notassociated_total,
sum(case when x.apply_bill_way='BW001' then x.apply_sum_money else 0 end ) notassociated_money,
sum(case when x.apply_bill_way='BW002' then 1 else 0 end ) barter_total,
sum(case when x.apply_bill_way='BW002' then x.apply_sum_money else 0 end ) barter_money,
sum(case when x.APPLY_BILL_TYPE='BT001' then 1 else 0 end ) bill_count_1,
sum(case when x.APPLY_BILL_TYPE='BT001' then x.apply_sum_money else 0 end ) bill_money_1,
sum(case when x.APPLY_BILL_TYPE='BT002' then 1 else 0 end ) bill_count_2,
sum(case when x.APPLY_BILL_TYPE='BT002' then x.apply_sum_money else 0 end ) bill_money_2
from cf_bill_apply_info x
where x.apply_state = 'ST003'
and x.apply_type='AT001' and x.apply_bill_way in ('BW001','BW002')
and to_char(x.apply_date,'yyyy-mm')='2020-12'
group by x.apply_company_code,x.apply_company_name) v1 on v1.apply_company_code=cr.profitcenter_code group by cr.profitcenter_group_code,cr.profitcenter_group_name
外层重新分组了，v1中sum字段，重新sum
select cr.profitcenter_group_code ,cr.profitcenter_name ,
sum(v1.amount_money) as amount_money,sum(v1.amount_total) as amount_total,sum(v1.predict_total) as predict_total,
sum(v1.associated_total) as associated_total,sum(v1.associated_money) as associated_money,sum(v1.associated_three_total) as associated_three_total,sum(v1.associated_three_money) as associated_three_money,
sum(v1.notassociated_total) as notassociated_total,sum(v1.notassociated_money) as notassociated_money,sum(v1.barter_total) as barter_total,sum(v1.barter_money) as barter_money,
sum(v1.bill_count_1) as bill_count_1,sum(v1.bill_money_1) as bill_money_1,sum(v1.bill_count_2) as bill_count_2,sum(v1.bill_money_2) as bill_money_2
from core_company cr
left join(select x.apply_company_code,x.apply_company_name,
sum(x.apply_sum_money) amount_money,count(*) amount_total,
sum(case when x.apply_bill_way='BW001' then 1 else 0 end ) predict_total,
sum(case when x.apply_bill_way='BW001' then x.apply_sum_money else 0 end ) predict_money,
sum(case when x.apply_bill_way='BW001' then 1 else 0 end ) associated_total,
sum(case when x.apply_bill_way='BW001' then x.apply_sum_money else 0 end ) associated_money,
sum(case when x.apply_bill_way='BW001' and sysdate-x.apply_date>90 then 1 else 0 end ) associated_three_total,
sum(case when x.apply_bill_way='BW001' and sysdate-x.apply_date>90 then x.apply_sum_money else 0 end ) associated_three_money,
sum(case when x.apply_bill_way='BW001' then 1 else 0 end ) notassociated_total,
sum(case when x.apply_bill_way='BW001' then x.apply_sum_money else 0 end ) notassociated_money,
sum(case when x.apply_bill_way='BW002' then 1 else 0 end ) barter_total,
sum(case when x.apply_bill_way='BW002' then x.apply_sum_money else 0 end ) barter_money,
sum(case when x.APPLY_BILL_TYPE='BT001' then 1 else 0 end ) bill_count_1,
sum(case when x.APPLY_BILL_TYPE='BT001' then x.apply_sum_money else 0 end ) bill_money_1,
sum(case when x.APPLY_BILL_TYPE='BT002' then 1 else 0 end ) bill_count_2,
sum(case when x.APPLY_BILL_TYPE='BT002' then x.apply_sum_money else 0 end ) bill_money_2
from cf_bill_apply_info x
where x.apply_state = 'ST003'
and x.apply_type='AT001' and x.apply_bill_way in ('BW001','BW002')
and to_char(x.apply_date,'yyyy-mm')='2020-12'
group by x.apply_company_code,x.apply_company_name) v1 on v1.apply_company_code=cr.profitcenter_code group by cr.profitcenter_group_code,cr.profitcenter_group_name