多个表关联查询，取某些字段，然后进行判断计算，生成新的字段，存储到另一张表中。
SELECT a.LOAN_RECEIPT_NBR,c.`NAME`,b.LOAN_INIT_PRIN *0.9,b.REGISTER_DATE,b.LOAN_EXPIRE_DATE,a.POST_DATE,a.PAY_AMT*0.9,a.CONSUMER_TRANS_ID,SUM(IF(d.POSTING_FLAG='F00' and TXN_CODE in('L804','L802','L708','L700','L702','L740') ,d.TXN_AMT*0.9,0)) as POST_PRIN,SUM(IF(d.POSTING_FLAG='F00' and TXN_CODE in('L750','L720','L744','L704') ,d.TXN_AMT*0.9,0)) as POST_INT,SUM(IF(d.POSTING_FLAG='F00' and TXN_CODE in('L710','L730','L746','L706') ,d.TXN_AMT*0.9,0)) as POST_PENALTY,SUM(IF(d.POSTING_FLAG='F00' and TXN_CODE in('L740') ,d.TXN_AMT*0.9,0)) as REPLACE_PAYfrom TM_LOAN_RECEIPT_LIST a left join TM_LOAN b on a.LOAN_RECEIPT_NBR = b.LOAN_RECEIPT_NBRLEFT JOIN TM_APP_PRIM_APPLICANT_INFO c on c.CARD_NO =b.CARD_NO LEFT JOIN TT_TXN_POST d on a.CONSUMER_TRANS_ID = d.CONSUMER_TRANS_ID and a.SYS_TRANS_ID = d.SYS_TRANS_ID
INSERT INTO rrs_bdm_udf_balance (BALANCE,DATA_DT,ACCT_NO,PAYMENT_COUNT,PAYMENT_AMT,REPAYMENT_COUNT,REPAYMENT_AMT,PAYMENT_CANCEL_AMT,PAYMENT_CANCEL_COUNT,REPAYMENT_CANCEL_AMT,REPAYMENT_CANCEL_COUNT,OTHER_CREDIT_AMT,OTHER_DEBIT_AMT)SELECT a.BALANCE,a.DATA_DT,a.ACCT_NO,COUNT(case WHEN b.DATA_TYPE='1' then b.DATA_TYPE END) as PAYMENT_COUNT,SUM(IF(b.DATA_TYPE='1',b.AMOUNT,0)) as PAYMENT_AMT,COUNT(case WHEN b.DATA_TYPE='3' then b.DATA_TYPE END) as REPAYMENT_COUNT,SUM(IF(b.DATA_TYPE='3',b.AMOUNT,0)) as REPAYMENT_AMT,COUNT(case WHEN b.DATA_TYPE='2' then b.DATA_TYPE END) as PAYMENT_CANCEL_COUNT,SUM(IF(b.DATA_TYPE='2',b.AMOUNT,0)) as PAYMENT_CANCEL_AMT,COUNT(case WHEN b.DATA_TYPE='4' then b.DATA_TYPE END) as REPAYMENT_CANCEL_COUNT,SUM(IF(b.DATA_TYPE='4',b.AMOUNT,0)) as REPAYMENT_CANCEL_AMT,SUM(IF(b.DATA_TYPE='5',b.AMOUNT,0)) as OTHER_CREDIT_AMT,SUM(IF(b.DATA_TYPE='6',b.AMOUNT,0)) as OTHER_DEBIT_AMTfrom RRS_BDM_UDF_CLEAR_TOT a left join RRS_BDM_UDF_CLEAR_DETAIL b on a.ACCT_NO = b.ACCT_NO
