在SQL文中进行加减乘除计算 Deciaml

#在SQL文中进行加减乘除计算

create table temp(
SELECT
(@rowNO := @rowNo+1) AS ‘序号’,
a.apply_date AS ‘申请日期’,
a.merchant_name AS ‘申请人’,
IF(a.loan_amount is null,0,CAST(a.loan_amount/100 as DECIMAL(10,2))) as ‘打款金额’,
a.finance_name as’资金来源’,
IF(a.loan_amount is null,0,CAST(a.caution_amount/100 as DECIMAL(10,2))) as ‘保证金’,
a.loan_term as ‘期限’,
CONCAT(CAST(a.service_rate as DECIMAL(10,2)),’’,’%’) as ‘利率’,
a.loan_date as ‘打款日期’,
CAST((IF(a.loan_amount is null,0,a.loan_amount)-IF(a.caution_amount is null,0,a.caution_amount))/100 as DECIMAL(10,2)) as ‘存量’,
b.expect_repay_date as ‘应还款日’,
b.actual_repay_date as ‘实际还款日’,
CAST((IF(b.bill_amount is null,0,b.bill_amount) + IF(b.expire_fee is null,0,b.expire_fee) - IF(b.free_fee is null,0,b.free_fee))/100 as DECIMAL(10,2)) as ‘实际还款金额’,
CAST(IF(b.expire_fee is null,0,b.expire_fee)/100 as DECIMAL(10,2)) as ‘逾期金额’,
CAST(IF(b.free_fee is null,0,b.free_fee)/100 as DECIMAL(10,2)) as ‘免息费’,
CASE b.status
WHEN ‘WAIT_REPAY’ THEN
‘待还款’
WHEN ‘EXPIRED’ THEN
‘已逾期’
WHEN ‘PAYED’ THEN
‘已还款’
ELSE
‘已取消’
END as ‘账单状态’
from pro_apply_bill as a,pro_bill as b,(select @rowNO :=0) as c
WHERE a.apply_no = b.apply_no and a.status !=‘CANCEL’
)