【工作问题】略复杂Sql实现：如何把业务逻辑揉进Sql语句中？

场景

最近在做公司的泛微二次开发，遇到了一个算麻烦的问题。有个需求大概是这样：返回实时计算剩余额度的开票公司主体列表。按一般MVC逻辑就很简单，先把公司主体列表全部取出来，再for循环一次遍历按照各种不同的条件计算好额度赋值后，返回给前端。

但泛微这边不行（开发过的兄弟应该都懂…），因为前端经过了深度的封装，无法通过调用外部接口和传参进行实现该需求。那剩下的只有一个办法，就是业务逻辑全部通过sql实现，泛微前端通过调用该sql和传参，进行实时计算后返回。

拆分需求

一开始我觉得这是无法实现的，哪有业务逻辑写在sql里的？（心里一万句草泥马…）

但冷静下来后想了想，其实是可以的，毕竟sql里也是有if语句，时间控制between也可以做到，通过left join将其余各表的数据取过来sum加减一下，再将这个逻辑group运用到每个主体上。

逻辑看起来非常乱，但其实每一步简化下来，思路还是很清晰的。

实现

1、首先，取公司主体表的数据：

SELECT 	a.id,
		a.hm,
		a.gsmc,
		a.skzhkh,
		a.mmoney FROM uf_mainmanagement

2、开始Left join每个需要加入计算的表

SELECT 	a.id,
		a.hm,
		a.gsmc,
		a.skzhkh,
		a.mmoney
FROM uf_mainmanagement a
LEFT JOIN uf_billingrecord b ON a.id = b.oursubject
LEFT JOIN uf_invoice_record c ON a.id = c.gszt
LEFT JOIN uf_oainvoice_return d ON a.id = d.kpzt
....

3、开始根据时间、额度细化计算每张表的所需数据

SELECT a.id,a.remainamount,a.oursubject 
from uf_billingrecord a 
left join uf_mainmanagement b on a.oursubject = b.id 
where invoicedate BETWEEN 
IF(b.type=1,date_sub('2020-09-24',interval 1 year),date(concat(year('2020-09-24'),'-',elt(quarter('2020-09-24'),1,4,7,10),'-',1))) 
and 
IF(b.type=1,'2020-09-24',LAST_DAY(MAKEDATE(EXTRACT(YEAR  FROM '2020-09-24'),1) + interval QUARTER('2020-09-24')*3-1 month))

通过left join了主体获取到了该条数据主体的额度类型和开票时间，根据额度类型不同，时间计算方式也不同，比如说：额度类型为年度的计算公式为：

剩余额度 = 总额度 - 已开票额度（时间倒推一年到今天） - 已预约额度（无时间限制）

而额度类型为季度的计算公式为：

剩余额度 = 总额度 - 已开票额度（当前时间的季度第一天到季度最后一天） - 已预约额度（当前时间的季度第一天到季度最后一天）

通过MYSQL的if语句就可以实现上面的需求：

IF(b.type=1,时间计算公式1,时间计算公式2) 

单独的一张表搞定了，接下来就简单了：
只需要将一样的逻辑放到每个left join中就行了

SELECT 	a.id,
		a.hm,
		a.gsmc,
		a.skzhkh,
		a.mmoney
FROM uf_mainmanagement a
LEFT JOIN 
	(SELECT a.id,a.remainamount,a.oursubject 
	FROM uf_billingrecord a 
	LEFT JOIN uf_mainmanagement b 
	ON a.oursubject = b.id 
	WHERE invoicedate BETWEEN 
	IF(b.type=1,date_sub('2020-09-24',interval 1 year),date(
	concat(year('2020-09-24'),'-',elt(quarter('2020-09-24'),1,4,7,10),'-',1))) 
	AND
	IF(b.type=1,'2020-09-24',
	interval QUARTER('2020-09-24')*3-1 month))) b 
	ON a.id = b.oursubject
LEFT JOIN
    (...)
....

4、取出已根据时间计算好的数据，sum加起来加入到公式中：
null的数据置0，否则报错
最后把索引加上

SELECT
	a.id,
	a.hm,
	a.gsmc,
	a.skzhkh,
	a.mmoney,
	(case a.type when '1' then '年度' when '2' then '季度' ELSE '不受限' end ) type,
	a.mmoney - sum( IFNULL( f.bckpje, 0 ) ) - sum( IFNULL( b.remainamount, 0 ) ) - sum( IFNULL( c.kpje, 0 ) ) + sum( IFNULL( d.tpje, 0 ) ) + sum( IFNULL( e.kpje, 0 ) ) AS remainamount,
	sum( IFNULL( f.bckpje, 0 ) ) + sum( IFNULL( b.remainamount, 0 ) ) + sum( IFNULL( c.kpje, 0 ) ) as usedamount
FROM
    ...

总结

sql总塞入业务逻辑其实是不可取的，一个是不好维护，二是可阅读性太差了，三是再复杂一点的业务逻辑（多加几个if else那种）都无法使用设计模式实现。

但这次的需求确实让我对sql语句有进一步的了解，也让我体会到了spring开发模式的便利呜呜呜…