mysql做报表查询。sum函数count函数的使用

多个表关联查询，取某些字段，然后进行判断计算，生成新的字段，存储到另一张表中。

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_PAY
from TM_LOAN_RECEIPT_LIST a left join  TM_LOAN b on a.LOAN_RECEIPT_NBR = b.LOAN_RECEIPT_NBR
LEFT 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_AMT
from RRS_BDM_UDF_CLEAR_TOT a left join  RRS_BDM_UDF_CLEAR_DETAIL b on a.ACCT_NO = b.ACCT_NO