MySQL连表更新事件维护优化
数据库  /  管理员 发布于 4年前   492
优化前:
-- 备忘备注:超过账期宽限期未结款,变更机构状态“已冻结”;结清恢复; 合同授信过期按上一次合同账期,屏蔽临时授信 UPDATE bota_corper as a, ( SELECT bm.corperid,bm._corperid, sum(if(bm.settlestatus=0,1,0)) num, (select IFNULL(sum(remain),0) from bota_corper_credit as ct where ct.corperid = bm.corperid and ct.type=1 ) as ctremain FROM `bota_corper_bill_month` bm inner join `bota_corper` c on bm.corperid = c.id where date(date_add(CONCAT(bm.month,'-',c.expired),INTERVAL 1 MONTH)) < date(now()) group by corperid ) as b SET a.status = if(b.num > 0 && a.remain < 0,4,3), a._status = if(b.num > 0 && a.remain < 0,'已冻结','正常') WHERE a.id = b.corperid and b.ctremain > 0
优化后:拆分两步利于维护
-- 事件名称:机构状态自动冻结
UPDATE bota_corper as C,
(
SELECT DISTINCT
A.corperid
FROM
bota_corper_credit A,
bota_corper_bill_month B
WHERE
A.type = 1
AND
A.corperid = B.corperid
AND B.settlestatus = 0
AND B.presettle < CURDATE()
) as D
SET
C.status = 4,
C._status = '已冻结'
WHERE
C.id = D.corperid
-- 事件名称:机构状态自动解冻
UPDATE bota_corper as C,
(
SELECT DISTINCT
A.corperid
FROM
bota_corper_credit A,
bota_corper_bill_month B
WHERE
A.type = 1
AND
A.corperid = B.corperid
AND B.settlestatus = 1
) as D
SET
C.status = 3,
C._status = '正常'
WHERE
C.id = D.corperid
路人 在
php中使用hyperf框架调用讯飞星火大模型实现国内版chatgpt功能示例中评论 教程很详细,如果加个前端chatgpt对话页面就完美了..博主 在
科学上网翻墙之v2rayN-Core客户端免费公益节点使用教程中评论 @ mashrdn 多切换几个节点测试,免费ssr是没那么稳..mashrdn 在
科学上网翻墙之v2rayN-Core客户端免费公益节点使用教程中评论 V2rayn免费节点添加上去了,youtobe无法打开网页,是怎么回事..张伟 在
科学上网翻墙之v2rayN-Core客户端免费公益节点使用教程中评论 3q!有用,不过免费节点隔天就要去git上复制新的导进去..博主 在
科学上网翻墙访问Google , 上外网神器佛跳墙VPN(永久免费)使用流程步骤中评论 该篇教程已不能用了,告知大家,免的老有老铁问我!..
Copyright·© 2019 侯体宗版权所有·
粤ICP备20027696号