sql的加减乘除运算_sql查询，sql加减乘除运算，sql函数

好记性不如写成博客

公司要做个台帐报表，用到sql加减乘除运算，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'

)