动态SQL拼接

drop table if exists general_202012_1;
drop table if exists general_202012_2;
drop table if exists general_202012_3;
drop table if exists general_202012_4;
drop table if exists general_202012_output;
drop table if exists general_income;
drop table if exists general_202012_final;


-- 所有字段合并宽表
drop table if exists general_2019_1;
create table general_2019_1 as
select
a.comcode,
a.inducode,
coalesce(b.TOTALLIAB_W,c.TOTALLIAB_W) TOTALLIAB_W,
coalesce(b.TOTALASSET,c.TOTALASSET) TOTALASSET,
coalesce(b.MONETARYFUND,c.MONETARYFUND) MONETARYFUND,
coalesce(b.PAYSHORTBOND,c.PAYSHORTBOND) PAYSHORTBOND,
coalesce(b.OTHERCURLIAB,c.OTHERCURLIAB) OTHERCURLIAB,
coalesce(b.BONDPAY,c.BONDPAY) BONDPAY,
coalesce(b.OTHEQUIN,c.OTHEQUIN) OTHEQUIN,
coalesce(b.TOTCONSTRUCTIONPROGRESS,c.TOTCONSTRUCTIONPROGRESS) TOTCONSTRUCTIONPROGRESS,
coalesce(b.MINORITYINTEREST,c.MINORITYINTEREST) MINORITYINTEREST
from cams_etl_camsfinancecomindustry a 
left join (
select comcode, TOTALLIAB as TOTALLIAB_W, TOTALASSET, MONETARYFUND, PAYSHORTBOND, OTHERCURLIAB, BONDPAY, OTHEQUIN, TOTCONSTRUCTIONPROGRESS, MINORITYINTEREST
from com_wzxbalancesheet
where reporttype = '102' and dataflag = 'C' and enddate = '2019-12-31'
) b
on a.comcode = b.comcode
left join (
select comcode, TOTALLIAB as TOTALLIAB_W, TOTALASSET, MONETARYFUND, PAYSHORTBOND, OTHERCURLIAB, BONDPAY, OTHEQUIN, TOTCONSTRUCTIONPROGRESS, MINORITYINTEREST
from com_wzxbalancesheet
where reporttype = '102' and dataflag = 'A' and enddate = '2019-12-31'
) c
on a.comcode = c.comcode
where a.isvalid = '1';

drop TABLE if exists general_2019_2;
create table general_2019_2 as
select
a.*, 
coalesce(b.SUBCOMPAYPROFIT_2017,c.SUBCOMPAYPROFIT_2017) SUBCOMPAYPROFIT_2017,
coalesce(b.SUBTOTALCASHINOPER_2017,c.SUBTOTALCASHINOPER_2017) SUBTOTALCASHINOPER_2017,
coalesce(b.SUBTOTALCASHOUTOPER_2017,c.SUBTOTALCASHOUTOPER_2017) SUBTOTALCASHOUTOPER_2017,
coalesce(b.CASHPAIDDIVIPROFINTE_2017,c.CASHPAIDDIVIPROFINTE_2017) CASHPAIDDIVIPROFINTE_2017,
coalesce(b.SUBCOMPAYPROFIT_2018,c.SUBCOMPAYPROFIT_2018) SUBCOMPAYPROFIT_2018,
coalesce(b.SUBTOTALCASHINOPER_2018,c.SUBTOTALCASHINOPER_2018) SUBTOTALCASHINOPER_2018,
coalesce(b.SUBTOTALCASHOUTOPER_2018,c.SUBTOTALCASHOUTOPER_2018) SUBTOTALCASHOUTOPER_2018,
coalesce(b.CASHPAIDDIVIPROFINTE_2018,c.CASHPAIDDIVIPROFINTE_2018) CASHPAIDDIVIPROFINTE_2018,
coalesce(b.SUBCOMPAYPROFIT_2019,c.SUBCOMPAYPROFIT_2019) SUBCOMPAYPROFIT_2019,
coalesce(b.SUBTOTALCASHINOPER_2019,c.SUBTOTALCASHINOPER_2019) SUBTOTALCASHINOPER_2019,
coalesce(b.SUBTOTALCASHOUTOPER_2019,c.SUBTOTALCASHOUTOPER_2019) SUBTOTALCASHOUTOPER_2019,
coalesce(b.CASHPAIDDIVIPROFINTE_2019,c.CASHPAIDDIVIPROFINTE_2019) CASHPAIDDIVIPROFINTE_2019
from general_2019_1 a
left join (
select
comcode, 
max(case when enddate = '2017-12-31'then SUBCOMPAYPROFIT end) 'SUBCOMPAYPROFIT_2017',
max(case when enddate = '2018-12-31'then SUBCOMPAYPROFIT end) 'SUBCOMPAYPROFIT_2018',
max(case when enddate = '2019-12-31'then SUBCOMPAYPROFIT end) 'SUBCOMPAYPROFIT_2019',
max(case when enddate = '2017-12-31'then SUBTOTALCASHINOPER end) 'SUBTOTALCASHINOPER_2017',
max(case when enddate = '2018-12-31'then SUBTOTALCASHINOPER end) 'SUBTOTALCASHINOPER_2018',
max(case when enddate = '2019-12-31'then SUBTOTALCASHINOPER end) 'SUBTOTALCASHINOPER_2019',
max(case when enddate = '2017-12-31'then SUBTOTALCASHOUTOPER end) 'SUBTOTALCASHOUTOPER_2017',
max(case when enddate = '2018-12-31'then SUBTOTALCASHOUTOPER end) 'SUBTOTALCASHOUTOPER_2018',
max(case when enddate = '2019-12-31'then SUBTOTALCASHOUTOPER end) 'SUBTOTALCASHOUTOPER_2019',
max(case when enddate = '2017-12-31'then CASHPAIDDIVIPROFINTE end) 'CASHPAIDDIVIPROFINTE_2017',
max(case when enddate = '2018-12-31'then CASHPAIDDIVIPROFINTE end) 'CASHPAIDDIVIPROFINTE_2018',
max(case when enddate = '2019-12-31'then CASHPAIDDIVIPROFINTE end) 'CASHPAIDDIVIPROFINTE_2019'
from com_wzxcashflow 
where reporttype='102' and dataflag='C' and enddate in ('2017-12-31','2018-12-31','2019-12-31')
group by comcode
) b
on a.comcode = b.comcode
left join(
select 
comcode,
max(case when enddate = '2017-12-31'then SUBCOMPAYPROFIT end) 'SUBCOMPAYPROFIT_2017',
max(case when enddate = '2018-12-31'then SUBCOMPAYPROFIT end) 'SUBCOMPAYPROFIT_2018',
max(case when enddate = '2019-12-31'then SUBCOMPAYPROFIT end) 'SUBCOMPAYPROFIT_2019',
max(case when enddate = '2017-12-31'then SUBTOTALCASHINOPER end) 'SUBTOTALCASHINOPER_2017',
max(case when enddate = '2018-12-31'then SUBTOTALCASHINOPER end) 'SUBTOTALCASHINOPER_2018',
max(case when enddate = '2019-12-31'then SUBTOTALCASHINOPER end) 'SUBTOTALCASHINOPER_2019',
max(case when enddate = '2017-12-31'then SUBTOTALCASHOUTOPER end) 'SUBTOTALCASHOUTOPER_2017',
max(case when enddate = '2018-12-31'then SUBTOTALCASHOUTOPER end) 'SUBTOTALCASHOUTOPER_2018',
max(case when enddate = '2019-12-31'then SUBTOTALCASHOUTOPER end) 'SUBTOTALCASHOUTOPER_2019',
max(case when enddate = '2017-12-31'then CASHPAIDDIVIPROFINTE end) 'CASHPAIDDIVIPROFINTE_2017',
max(case when enddate = '2018-12-31'then CASHPAIDDIVIPROFINTE end) 'CASHPAIDDIVIPROFINTE_2018',
max(case when enddate = '2019-12-31'then CASHPAIDDIVIPROFINTE end) 'CASHPAIDDIVIPROFINTE_2019'
from com_wzxcashflow 
where reporttype='102' and dataflag='A' and enddate in ('2017-12-31','2018-12-31','2019-12-31')
group by comcode
) c
on a.comcode = c.comcode;


drop table if exists general_income;
create table general_income AS
select b.*
from cams_etl_camsfinancecomindustry a
left join com_wzxincome b
on a.comcode = b.comcode;

drop TABLE if exists general_2019_3;
create table general_2019_3 as
select
a.*,
coalesce(b.TOTALOPERREVENUE_2016,c.TOTALOPERREVENUE_2016) TOTALOPERREVENUE_2016,
coalesce(b.TOTALPROFIT_2017,c.TOTALPROFIT_2017) TOTALPROFIT_2017,
coalesce(b.TOTALOPERREVENUE_2017,c.TOTALOPERREVENUE_2017) TOTALOPERREVENUE_2017,
coalesce(b.NETINCOMEFROMINV_2017,c.NETINCOMEFROMINV_2017) NETINCOMEFROMINV_2017,
coalesce(b.FAIRCHANGESNETINCOME_2017,c.FAIRCHANGESNETINCOME_2017) FAIRCHANGESNETINCOME_2017, 
coalesce(b.TOTALOPERCOST_2017,c.TOTALOPERCOST_2017) TOTALOPERCOST_2017,
coalesce(b.ADMINEXPENSE_2017,c.ADMINEXPENSE_2017) ADMINEXPENSE_2017,
coalesce(b.SELLEXPENSE_2017,c.SELLEXPENSE_2017) SELLEXPENSE_2017, 
coalesce(b.FINACOST_2017,c.FINACOST_2017) FINACOST_2017,
coalesce(b.CAPDEVELOPCOST_2017,c.CAPDEVELOPCOST_2017) CAPDEVELOPCOST_2017,
coalesce(b.TOTALPROFIT_2018,c.TOTALPROFIT_2018) TOTALPROFIT_2018,
coalesce(b.TOTALOPERREVENUE_2018,c.TOTALOPERREVENUE_2018) TOTALOPERREVENUE_2018,
coalesce(b.NETINCOMEFROMINV_2018,c.NETINCOMEFROMINV_2018) NETINCOMEFROMINV_2018,
coalesce(b.FAIRCHANGESNETINCOME_2018,c.FAIRCHANGESNETINCOME_2018) FAIRCHANGESNETINCOME_2018, 
coalesce(b.TOTALOPERCOST_2018,c.TOTALOPERCOST_2018) TOTALOPERCOST_2018,
coalesce(b.ADMINEXPENSE_2018,c.ADMINEXPENSE_2018) ADMINEXPENSE_2018,
coalesce(b.SELLEXPENSE_2018,c.SELLEXPENSE_2018) SELLEXPENSE_2018, 
coalesce(b.FINACOST_2018,c.FINACOST_2018) FINACOST_2018,
coalesce(b.CAPDEVELOPCOST_2018,c.CAPDEVELOPCOST_2018) CAPDEVELOPCOST_2018,
coalesce(b.TOTALPROFIT_2019,c.TOTALPROFIT_2019) TOTALPROFIT_2019,
coalesce(b.TOTALOPERREVENUE_2019,c.TOTALOPERREVENUE_2019) TOTALOPERREVENUE_2019,
coalesce(b.NETINCOMEFROMINV_2019,c.NETINCOMEFROMINV_2019) NETINCOMEFROMINV_2019,
coalesce(b.FAIRCHANGESNETINCOME_2019,c.FAIRCHANGESNETINCOME_2019) FAIRCHANGESNETINCOME_2019, 
coalesce(b.TOTALOPERCOST_2018,c.TOTALOPERCOST_2019) TOTALOPERCOST_2019,
coalesce(b.ADMINEXPENSE_2019,c.ADMINEXPENSE_2019) ADMINEXPENSE_2019,
coalesce(b.SELLEXPENSE_2019,c.SELLEXPENSE_2019) SELLEXPENSE_2019, 
coalesce(b.FINACOST_2019,c.FINACOST_2019) FINACOST_2019,
coalesce(b.CAPDEVELOPCOST_2019,c.CAPDEVELOPCOST_2019) CAPDEVELOPCOST_2019,
coalesce(b.NONOPERREVENUE_2017,c.NONOPERREVENUE_2017) NONOPERREVENUE_2017,
coalesce(b.NONOPEREXPENSE_2017,c.NONOPEREXPENSE_2017) NONOPEREXPENSE_2017,
coalesce(b.NONOPERREVENUE_2018,c.NONOPERREVENUE_2018) NONOPERREVENUE_2018,
coalesce(b.NONOPEREXPENSE_2018,c.NONOPEREXPENSE_2018) NONOPEREXPENSE_2018,
coalesce(b.NONOPERREVENUE_2019,c.NONOPERREVENUE_2019) NONOPERREVENUE_2019,
coalesce(b.NONOPEREXPENSE_2019,c.NONOPEREXPENSE_2019) NONOPEREXPENSE_2019,
coalesce(b.OPERREVENUE_2017,c.OPERREVENUE_2017) OPERREVENUE_2017,
coalesce(b.OPERCOST_2017,c.OPERCOST_2017) OPERCOST_2017,
coalesce(b.OPERREVENUE_2018,c.OPERREVENUE_2018) OPERREVENUE_2018,
coalesce(b.OPERCOST_2018,c.OPERCOST_2018) OPERCOST_2018,
coalesce(b.OPERREVENUE_2019,c.OPERREVENUE_2019) OPERREVENUE_2019,
coalesce(b.OPERCOST_2019,c.OPERCOST_2019) OPERCOST_2019,
0 as'本公司当年分红_2016',
0 as'本公司当年分红_2017',
0 as'本公司当年分红_2018',
0 as'本公司当年分红_2019'
from general_2019_2 a
left join (
select
comcode, 
max(case when enddate = '2017-12-31'then TOTALPROFIT end) 'TOTALPROFIT_2017',
max(case when enddate = '2018-12-31'then TOTALPROFIT end) 'TOTALPROFIT_2018',
max(case when enddate = '2019-12-31'then TOTALPROFIT end) 'TOTALPROFIT_2019',
max(case when enddate = '2016-12-31'then TOTALOPERREVENUE end) 'TOTALOPERREVENUE_2016',
max(case when enddate = '2017-12-31'then TOTALOPERREVENUE end) 'TOTALOPERREVENUE_2017',
max(case when enddate = '2018-12-31'then TOTALOPERREVENUE end) 'TOTALOPERREVENUE_2018',
max(case when enddate = '2019-12-31'then TOTALOPERREVENUE end) 'TOTALOPERREVENUE_2019',
max(case when enddate = '2017-12-31'then NETINCOMEFROMINV end) 'NETINCOMEFROMINV_2017',
max(case when enddate = '2018-12-31'then NETINCOMEFROMINV end) 'NETINCOMEFROMINV_2018',
max(case when enddate = '2019-12-31'then NETINCOMEFROMINV end) 'NETINCOMEFROMINV_2019',
max(case when enddate = '2017-12-31'then FAIRCHANGESNETINCOME end) 'FAIRCHANGESNETINCOME_2017',
max(case when enddate = '2018-12-31'then FAIRCHANGESNETINCOME end) 'FAIRCHANGESNETINCOME_2018',
max(case when enddate = '2019-12-31'then FAIRCHANGESNETINCOME end) 'FAIRCHANGESNETINCOME_2019',
max(case when enddate = '2017-12-31'then TOTALOPERCOST end) 'TOTALOPERCOST_2017',
max(case when enddate = '2018-12-31'then TOTALOPERCOST end) 'TOTALOPERCOST_2018',
max(case when enddate = '2019-12-31'then TOTALOPERCOST end) 'TOTALOPERCOST_2019',
max(case when enddate = '2017-12-31'then ADMINEXPENSE end) 'ADMINEXPENSE_2017',
max(case when enddate = '2018-12-31'then ADMINEXPENSE end) 'ADMINEXPENSE_2018',
max(case when enddate = '2019-12-31'then ADMINEXPENSE end) 'ADMINEXPENSE_2019',
max(case when enddate = '2017-12-31'then SELLEXPENSE end) 'SELLEXPENSE_2017',
max(case when enddate = '2018-12-31'then SELLEXPENSE end) 'SELLEXPENSE_2018',
max(case when enddate = '2019-12-31'then SELLEXPENSE end) 'SELLEXPENSE_2019',
max(case when enddate = '2017-12-31'then FINACOST end) 'FINACOST_2017',
max(case when enddate = '2018-12-31'then FINACOST end) 'FINACOST_2018',
max(case when enddate = '2019-12-31'then FINACOST end) 'FINACOST_2019',
max(case when enddate = '2017-12-31'then CAPDEVELOPCOST end) 'CAPDEVELOPCOST_2017',
max(case when enddate = '2018-12-31'then CAPDEVELOPCOST end) 'CAPDEVELOPCOST_2018',
max(case when enddate = '2019-12-31'then CAPDEVELOPCOST end) 'CAPDEVELOPCOST_2019',
max(case when enddate = '2017-12-31'then NONOPERREVENUE end) 'NONOPERREVENUE_2017',
max(case when enddate = '2018-12-31'then NONOPERREVENUE end) 'NONOPERREVENUE_2018',
max(case when enddate = '2019-12-31'then NONOPERREVENUE end) 'NONOPERREVENUE_2019',
max(case when enddate = '2017-12-31'then NONOPEREXPENSE end) 'NONOPEREXPENSE_2017',
max(case when enddate = '2018-12-31'then NONOPEREXPENSE end) 'NONOPEREXPENSE_2018',
max(case when enddate = '2019-12-31'then NONOPEREXPENSE end) 'NONOPEREXPENSE_2019',
max(case when enddate = '2017-12-31'then OPERREVENUE end) 'OPERREVENUE_2017',
max(case when enddate = '2018-12-31'then OPERREVENUE end) 'OPERREVENUE_2018',
max(case when enddate = '2019-12-31'then OPERREVENUE end) 'OPERREVENUE_2019',
max(case when enddate = '2017-12-31'then OPERCOST end) 'OPERCOST_2017',
max(case when enddate = '2018-12-31'then OPERCOST end) 'OPERCOST_2018',
max(case when enddate = '2019-12-31'then OPERCOST end) 'OPERCOST_2019'
from general_income 
where reporttype='102' and dataflag='C' and enddate in ('2016-12-31','2017-12-31','2018-12-31','2019-12-31')
group by comcode
) b
on a.comcode = b.comcode
left join (
select
comcode, 
max(case when enddate = '2017-12-31'then TOTALPROFIT end) 'TOTALPROFIT_2017',
max(case when enddate = '2018-12-31'then TOTALPROFIT end) 'TOTALPROFIT_2018',
max(case when enddate = '2019-12-31'then TOTALPROFIT end) 'TOTALPROFIT_2019',
max(case when enddate = '2016-12-31'then TOTALOPERREVENUE end) 'TOTALOPERREVENUE_2016',
max(case when enddate = '2017-12-31'then TOTALOPERREVENUE end) 'TOTALOPERREVENUE_2017',
max(case when enddate = '2018-12-31'then TOTALOPERREVENUE end) 'TOTALOPERREVENUE_2018',
max(case when enddate = '2019-12-31'then TOTALOPERREVENUE end) 'TOTALOPERREVENUE_2019',
max(case when enddate = '2017-12-31'then NETINCOMEFROMINV end) 'NETINCOMEFROMINV_2017',
max(case when enddate = '2018-12-31'then NETINCOMEFROMINV end) 'NETINCOMEFROMINV_2018',
max(case when enddate = '2019-12-31'then NETINCOMEFROMINV end) 'NETINCOMEFROMINV_2019',
max(case when enddate = '2017-12-31'then FAIRCHANGESNETINCOME end) 'FAIRCHANGESNETINCOME_2017',
max(case when enddate = '2018-12-31'then FAIRCHANGESNETINCOME end) 'FAIRCHANGESNETINCOME_2018',
max(case when enddate = '2019-12-31'then FAIRCHANGESNETINCOME end) 'FAIRCHANGESNETINCOME_2019',
max(case when enddate = '2017-12-31'then TOTALOPERCOST end) 'TOTALOPERCOST_2017',
max(case when enddate = '2018-12-31'then TOTALOPERCOST end) 'TOTALOPERCOST_2018',
max(case when enddate = '2019-12-31'then TOTALOPERCOST end) 'TOTALOPERCOST_2019',
max(case when enddate = '2017-12-31'then ADMINEXPENSE end) 'ADMINEXPENSE_2017',
max(case when enddate = '2018-12-31'then ADMINEXPENSE end) 'ADMINEXPENSE_2018',
max(case when enddate = '2019-12-31'then ADMINEXPENSE end) 'ADMINEXPENSE_2019',
max(case when enddate = '2017-12-31'then SELLEXPENSE end) 'SELLEXPENSE_2017',
max(case when enddate = '2018-12-31'then SELLEXPENSE end) 'SELLEXPENSE_2018',
max(case when enddate = '2019-12-31'then SELLEXPENSE end) 'SELLEXPENSE_2019',
max(case when enddate = '2017-12-31'then FINACOST end) 'FINACOST_2017',
max(case when enddate = '2018-12-31'then FINACOST end) 'FINACOST_2018',
max(case when enddate = '2019-12-31'then FINACOST end) 'FINACOST_2019',
max(case when enddate = '2017-12-31'then CAPDEVELOPCOST end) 'CAPDEVELOPCOST_2017',
max(case when enddate = '2018-12-31'then CAPDEVELOPCOST end) 'CAPDEVELOPCOST_2018',
max(case when enddate = '2019-12-31'then CAPDEVELOPCOST end) 'CAPDEVELOPCOST_2019',
max(case when enddate = '2017-12-31'then NONOPERREVENUE end) 'NONOPERREVENUE_2017',
max(case when enddate = '2018-12-31'then NONOPERREVENUE end) 'NONOPERREVENUE_2018',
max(case when enddate = '2019-12-31'then NONOPERREVENUE end) 'NONOPERREVENUE_2019',
max(case when enddate = '2017-12-31'then NONOPEREXPENSE end) 'NONOPEREXPENSE_2017',
max(case when enddate = '2018-12-31'then NONOPEREXPENSE end) 'NONOPEREXPENSE_2018',
max(case when enddate = '2019-12-31'then NONOPEREXPENSE end) 'NONOPEREXPENSE_2019',
max(case when enddate = '2017-12-31'then OPERREVENUE end) 'OPERREVENUE_2017',
max(case when enddate = '2018-12-31'then OPERREVENUE end) 'OPERREVENUE_2018',
max(case when enddate = '2019-12-31'then OPERREVENUE end) 'OPERREVENUE_2019',
max(case when enddate = '2017-12-31'then OPERCOST end) 'OPERCOST_2017',
max(case when enddate = '2018-12-31'then OPERCOST end) 'OPERCOST_2018',
max(case when enddate = '2019-12-31'then OPERCOST end) 'OPERCOST_2019'
from general_income 
where reporttype='102' and dataflag='A' and enddate in ('2016-12-31','2017-12-31','2018-12-31','2019-12-31')
group by comcode
) c
on a.comcode = c.comcode;

drop TABLE if exists general_2019_4;
create table general_2019_4 as
select
a.*,
b.listed,
b.chiname,
c.totalliab,
c.netprofitinshareholder_2017,
c.netprofitinshareholder_2018,
c.netprofitinshareholder_2019,
d.shortdebtadd,
d.leftbnddivsdebt,
d.unlimitdivdebt,
d.grossprofitrate_2017,
d.grossprofitrate_2018, 
d.grossprofitrate_2019,
d.netprofitrate_2017,
d.netprofitrate_2018,
d.netprofitrate_2019,
f.exchangename
from general_2019_3 a
left join cams_etl_camsetlcompany b
on a.comcode = b.comcode
left join (
select comcode, 
max(case when enddate = '2019-12-31' then totalliab end) totalliab, 
max(case when enddate = '2019-12-31' then netprofitinshareholder end) netprofitinshareholder_2019,
max(case when enddate = '2018-12-31' then netprofitinshareholder end) netprofitinshareholder_2018,
max(case when enddate = '2017-12-31' then netprofitinshareholder end) netprofitinshareholder_2017 
from mf_com_finance_quality_new_q 
where enddate in ('2019-12-31','2018-12-31','2017-12-31') and dataflag = '102'
group by comcode
) c
on a.comcode = c.comcode
left join (
select comcode, 
max(case when enddate = '2019-12-31' then shortdebtadd end) shortdebtadd, 
max(case when enddate = '2019-12-31' then leftbnddivsdebt end) leftbnddivsdebt, 
max(case when enddate = '2019-12-31' then unlimitdivdebt end) unlimitdivdebt,
max(case when enddate = '2017-12-31' then grossprofitrate end) grossprofitrate_2017, 
max(case when enddate = '2018-12-31' then grossprofitrate end) grossprofitrate_2018, 
max(case when enddate = '2019-12-31' then grossprofitrate end) grossprofitrate_2019, 
max(case when enddate = '2017-12-31' then netprofitrate end) netprofitrate_2017, 
max(case when enddate = '2018-12-31' then netprofitrate end) netprofitrate_2018, 
max(case when enddate = '2019-12-31' then netprofitrate end) netprofitrate_2019
from mf_com_common_report_q where enddate in ( '2019-12-31','2018-12-31','2017-12-31')
group by comcode
) d 
on a.comcode = d.comcode
left join (
select comcode,GROUP_CONCAT(exchangename) exchangename 
from pub_securitiesmain 
where SECUCATEGORYCODEI = '10' and exchangecode in('101','105','161') and listingstatecode = '1'
group by 1
) f
on a.comcode = f.comcode;

drop table if exists general_2019_output;
create table general_2019_output as
select comcode, chiname, 
(case when listed = '1' then '是' else '否' end) '是否上市',
exchangename '上市交易所',
MINORITYINTEREST/(TOTALASSET-TOTALLIAB_W) '少数股东权益占比',
MINORITYINTEREST/100000000	'少数股东权益',
netprofitinshareholder_2017/100000000 '净利润_2017',
netprofitinshareholder_2018/100000000 '净利润_2018',
netprofitinshareholder_2019/100000000 '净利润_2019',
(TOTALASSET-TOTALLIAB_W)/100000000 as '净资产',
TOTALASSET/100000000 '总资产', TOTALLIAB_W/100000000 as '负债合计', 
TOTALLIAB_W/TOTALASSET '资产负债率',
MONETARYFUND/100000000 as '货币资金',
leftbnddivsdebt as '债券余额/有息债务',
leftbnddivsdebt*totalliab/100000000 as '债券余额',
PAYSHORTBOND/100000000 as '应付短期债券', OTHERCURLIAB/100000000 as '其他流动负债', BONDPAY/100000000 as '应付债券', OTHEQUIN/100000000 as '其他权益工具',
totalliab/100000000 as '总有息债务', shortdebtadd/totalliab as '考虑回售的短期有息债务/有息债务', shortdebtadd/100000000 as '考虑回售的短期有息债务',
TOTCONSTRUCTIONPROGRESS/TOTALASSET as '在建工程/总资产', TOTCONSTRUCTIONPROGRESS/100000000 as '在建工程(合计)', 
 (TOTALOPERREVENUE_2017-TOTALOPERREVENUE_2016)/TOTALOPERREVENUE_2017 as '营业总收入增速_2017',
(TOTALOPERREVENUE_2018-TOTALOPERREVENUE_2017)/TOTALOPERREVENUE_2018 as '营业总收入增速_2018',
(TOTALOPERREVENUE_2019-TOTALOPERREVENUE_2018)/TOTALOPERREVENUE_2019 as '营业总收入增速_2019',
TOTALOPERREVENUE_2016/100000000 as '营业总收入_2016',
TOTALOPERREVENUE_2017/100000000 as '营业总收入_2017',
TOTALOPERREVENUE_2018/100000000 as '营业总收入_2018',
TOTALOPERREVENUE_2019/100000000 as '营业总收入_2019',
grossprofitrate_2017 as '销售毛利率_2017',
grossprofitrate_2018 as '销售毛利率_2018',
grossprofitrate_2019 as '销售毛利率_2019',
TOTALOPERCOST_2017/100000000 as '营业总成本_2017',
TOTALOPERCOST_2018/100000000 as '营业总成本_2018',
TOTALOPERCOST_2019/100000000 as '营业总成本_2019',
(OPERREVENUE_2017-OPERCOST_2017)/OPERREVENUE_2017 as '销售毛利率_2017_计算',
(OPERREVENUE_2018-OPERCOST_2018)/OPERREVENUE_2018 as '销售毛利率_2018_计算',
(OPERREVENUE_2019-OPERCOST_2019)/OPERREVENUE_2019 as '销售毛利率_2019_计算',
OPERREVENUE_2017/100000000 as '营业收入_2017',
OPERREVENUE_2018/100000000 as '营业收入_2018',
OPERREVENUE_2019/100000000 as '营业收入_2019',
OPERCOST_2017/100000000 as '营业成本_2017',
OPERCOST_2018/100000000 as '营业成本_2018',
OPERCOST_2019/100000000 as '营业成本_2019',
(coalesce(SELLEXPENSE_2017, 0)+coalesce(ADMINEXPENSE_2017, 0)+coalesce(FINACOST_2017, 0)+ coalesce(CAPDEVELOPCOST_2017, 0))/TOTALOPERREVENUE_2017 as '期间费用/营业总收入_2017',
(coalesce(SELLEXPENSE_2018, 0)+coalesce(ADMINEXPENSE_2018, 0)+coalesce(FINACOST_2018, 0)+ coalesce(CAPDEVELOPCOST_2018, 0))/TOTALOPERREVENUE_2018 as '期间费用/营业总收入_2018',
(coalesce(SELLEXPENSE_2019, 0)+coalesce(ADMINEXPENSE_2019, 0)+coalesce(FINACOST_2019, 0)+ coalesce(CAPDEVELOPCOST_2019, 0))/TOTALOPERREVENUE_2019 as '期间费用/营业总收入_2019',
SELLEXPENSE_2017/100000000 as '销售费用_2017',
SELLEXPENSE_2018/100000000 as '销售费用_2018',
SELLEXPENSE_2019/100000000 as '销售费用_2019',
ADMINEXPENSE_2017/100000000 as '管理费用_2017',
ADMINEXPENSE_2018/100000000 as '管理费用_2018',
ADMINEXPENSE_2019/100000000 as '管理费用_2019',
FINACOST_2017/100000000 as '财务费用_2017',
FINACOST_2018/100000000 as '财务费用_2018',
FINACOST_2019/100000000 as '财务费用_2019',
CAPDEVELOPCOST_2017/100000000 as '研发费用_2017',
CAPDEVELOPCOST_2018/100000000 as '研发费用_2018',
CAPDEVELOPCOST_2019/100000000 as '研发费用_2019',
TOTALPROFIT_2017/100000000 as '利润总额_2017',
TOTALPROFIT_2018/100000000 as '利润总额_2018',
TOTALPROFIT_2019/100000000 as '利润总额_2019',
NETINCOMEFROMINV_2017/100000000 as '投资净收益_2017',
NETINCOMEFROMINV_2018/100000000 as '投资净收益_2018',
NETINCOMEFROMINV_2019/100000000 as '投资净收益_2019',
FAIRCHANGESNETINCOME_2017/100000000 as '公允价值变动净收益_2017',
FAIRCHANGESNETINCOME_2018/100000000 as '公允价值变动净收益_2018',
FAIRCHANGESNETINCOME_2019/100000000 as '公允价值变动净收益_2019',
(SUBTOTALCASHINOPER_2017-SUBTOTALCASHOUTOPER_2017)/(coalesce(CASHPAIDDIVIPROFINTE_2017,0)-coalesce(SUBCOMPAYPROFIT_2017,0)-本公司当年分红_2016) as '经营活动净现金流/利息支出_2017',
(SUBTOTALCASHINOPER_2018-SUBTOTALCASHOUTOPER_2018)/(coalesce(CASHPAIDDIVIPROFINTE_2018,0)-coalesce(SUBCOMPAYPROFIT_2018,0)-本公司当年分红_2017) as '经营活动净现金流/利息支出_2018',
(SUBTOTALCASHINOPER_2019-SUBTOTALCASHOUTOPER_2019)/(coalesce(CASHPAIDDIVIPROFINTE_2019,0)-coalesce(SUBCOMPAYPROFIT_2019,0)-本公司当年分红_2018) as '经营活动净现金流/利息支出_2019',
CASHPAIDDIVIPROFINTE_2017/100000000 as '分配股利、利润或偿付利息支付的现金_2017',
CASHPAIDDIVIPROFINTE_2018/100000000 as '分配股利、利润或偿付利息支付的现金_2018',
CASHPAIDDIVIPROFINTE_2019/100000000 as '分配股利、利润或偿付利息支付的现金_2019', 
SUBCOMPAYPROFIT_2017/100000000 as '其中:子公司支付给少数股东的股利、利润_2017', 
SUBCOMPAYPROFIT_2018/100000000 as '其中:子公司支付给少数股东的股利、利润_2018',
SUBCOMPAYPROFIT_2019/100000000 as '其中:子公司支付给少数股东的股利、利润_2019',
NONOPERREVENUE_2017/100000000 as '营业外收入_2017',
NONOPERREVENUE_2018/100000000 as '营业外收入_2018',
NONOPERREVENUE_2019/100000000 as '营业外收入_2019',
NONOPEREXPENSE_2017/100000000 as '营业外支出_2017',
NONOPEREXPENSE_2018/100000000 as '营业外支出_2018',
NONOPEREXPENSE_2019/100000000 as '营业外支出_2019',
(coalesce(TOTALPROFIT_2017,0)-(coalesce(NONOPERREVENUE_2017,0)-coalesce(NONOPEREXPENSE_2017,0))-coalesce(NETINCOMEFROMINV_2017,0)-coalesce(FAIRCHANGESNETINCOME_2017,0))/TOTALOPERREVENUE_2017 as '扣非后净利率_2017',
(coalesce(TOTALPROFIT_2018,0)-(coalesce(NONOPERREVENUE_2018,0)-coalesce(NONOPEREXPENSE_2018,0))-coalesce(NETINCOMEFROMINV_2018,0)-coalesce(FAIRCHANGESNETINCOME_2018,0))/TOTALOPERREVENUE_2018 as '扣非后净利率_2018',
(coalesce(TOTALPROFIT_2019,0)-(coalesce(NONOPERREVENUE_2019,0)-coalesce(NONOPEREXPENSE_2019,0))-coalesce(NETINCOMEFROMINV_2019,0)-coalesce(FAIRCHANGESNETINCOME_2019,0))/TOTALOPERREVENUE_2019 as '扣非后净利率_2019',
coalesce(netprofitrate_2017,0)-(coalesce(NETINCOMEFROMINV_2017,0)+coalesce(FAIRCHANGESNETINCOME_2017,0))/coalesce(TOTALOPERREVENUE_2017,0) as '调整后净利率_2017',
coalesce(netprofitrate_2018,0)-(coalesce(NETINCOMEFROMINV_2018,0)+coalesce(FAIRCHANGESNETINCOME_2018,0))/coalesce(TOTALOPERREVENUE_2018,0) as '调整后净利率_2018',
coalesce(netprofitrate_2019,0)-(coalesce(NETINCOMEFROMINV_2019,0)+coalesce(FAIRCHANGESNETINCOME_2019,0))/coalesce(TOTALOPERREVENUE_2019,0) as '调整后净利率_2019',
netprofitrate_2017 as '销售净利率_2017',
netprofitrate_2018 as '销售净利率_2018',
netprofitrate_2019 as '销售净利率_2019',
本公司当年分红_2016,
本公司当年分红_2017,
本公司当年分红_2018,
本公司当年分红_2019,
SUBTOTALCASHINOPER_2017/100000000 as '经营活动现金流入小计_2017',
SUBTOTALCASHINOPER_2018/100000000 as '经营活动现金流入小计_2018',
SUBTOTALCASHINOPER_2019/100000000 as '经营活动现金流入小计_2019',
SUBTOTALCASHOUTOPER_2017/100000000 as '经营活动现金流出小计_2017',
SUBTOTALCASHOUTOPER_2018/100000000 as '经营活动现金流出小计_2018',
SUBTOTALCASHOUTOPER_2019/100000000 as '经营活动现金流出小计_2019'
from general_2019_4;

select * from general_2019_output;

drop table if exists general_2019_final;
create table general_2019_final AS
select
	a.*,
	b.`一级行业`,
	b.`二级行业`,
	b.`三级行业`
from general_2019_output a
left join
myd_camsindustry b
on a.comcode = b.comcode;

select * from general_2019_final;

select COLUMN_COMMENT,column_name,table_name from information_schema.`COLUMNS` where table_name = 'cams_etl_camsfinancecomindustry'

select * from cams_etl_camsfinancecomindustry a where a.isvalid = '1';

-- 第一大股东质押比例 -----------------------------------
drop table if exists generalfirstshper_2019_1;
create table generalfirstshper_2019_1 as
select a.*
from COM_SHAREHOLD a
join (select comcode,max(enddate)enddate from COM_SHAREHOLD group by 1)b 
  on a.comcode=b.comcode
 and a.enddate=b.enddate;


drop table if exists generalfirstshper_2019_2;
create table generalfirstshper_2019_2 as
select comcode,CONCERTEDGROUP,sum(SHAREHDNUM)SHAREHDNUM,sum(PLEDGENUM)PLEDGENUM from generalfirstshper_2019_1 
where CONCERTEDGROUP is not null or rank=1
group by 1,2;

drop table if exists generalfirstshper_2019_3;
create table generalfirstshper_2019_3 as
select a.*
from generalfirstshper_2019_2 a
join (select comcode,max(SHAREHDNUM)SHAREHDNUM from generalfirstshper_2019_2 group by 1)b
on a.comcode=b.comcode
and a.SHAREHDNUM=b.SHAREHDNUM;

drop table if exists generalfirstshper_2019_final;
create table generalfirstshper_2019_final as
select a.comcode,coalesce(PLEDGENUM,0)/SHAREHDNUM 大股东质押比例, a.*
from generalfirstshper_2019_3 a
left join generalfirstshper_2019_3 b
on a.comcode = b.comcode;