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

最新推荐文章于 2025-09-21 21:30:16 发布

原创最新推荐文章于 2025-09-21 21:30:16 发布 · 6.4w 阅读

30 ·

CC 4.0 BY-SA版权

文章标签：

#sql #sql函数 #sql运算

数据库专栏收录该内容

1 篇文章

订阅专栏

本文介绍了一种使用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'
		
)