昨天一下午就为了补业务数据,写SQL快崩溃了得...
直接贴不解释了
-- 先拿到[销售变更id]拿到[变更信息] -- 通过[变更信息]的主表id拿到[主表信息] -- 条件可以是 MAX(cc.id) + GROUP BY crId 按主表id分组拿最大的变更id -- 或者是 cc.oc_name = '第一次变更' 按变更名称筛选 SELECT cooc.* FROM sal_co_order AS coor JOIN sal_co_orchange AS cooc ON coor.id = cooc.sal_co_or_id WHERE or_co_code = 'XS23110001' ORDER BY cooc.id DESC LIMIT 1 -- 主表更新 变更的内容 -- 变更次数 + 1 orVaryNum -- 合同性质 orCoQuality -- 合同主体 orCoCoId -- 签约客户 sysArCuId -- 签约金额 orSignAmount -- 履约保证金 orEarnest -- 审批状态 -- 变更表 -- ocApprState 更新审批状态 UPDATE sal_co_order AS cr JOIN sal_co_orchange AS cc ON cr.id = cc.sal_co_or_id SET cr.ap_vary_num = cr.ap_vary_num + 1, cr.or_co_quality = cc.oc_aft_quality, cr.or_co_co_id = cc.oc_aft_co_id, cr.sys_ar_cu_id = cc.oc_aft_cu_id, cr.or_sign_amount = cc.oc_aft_amount, cr.or_earnest = cc.oc_aft_earnest, cr.or_sign_amount = cc.oc_aft_amount, cr.or_change_state = 1, cc.oc_appr_state = 1, cc.oc_name = '第一次变更' WHERE or_co_code = 'XS23110001' AND cc.oc_name = '第一次变更'; -- 变更附表 (商品 + 条款) -- 主表id + 变更id 查询当前集合 CREATE TABLE tmp_ocow AS SELECT wa.*, mx.ccId, mx.crId FROM sal_co_orware AS wa JOIN (SELECT cr.id AS crId, MAX(cc.id) AS ccId FROM sal_co_order AS cr JOIN sal_co_orchange AS cc ON cr.id = cc.sal_co_or_id WHERE or_co_code = 'XS23110001' GROUP BY crId) AS mx ON wa.sal_co_or_id = crId AND wa.sal_pr_oc_id = ccId; CREATE TABLE tmp_ocpa AS SELECT pa.*, mx.ccId, mx.crId FROM sal_co_payment AS pa JOIN (SELECT cr.id AS crId, MAX(cc.id) AS ccId FROM sal_co_order AS cr JOIN sal_co_orchange AS cc ON cr.id = cc.sal_co_or_id WHERE or_co_code = 'XS23110001' GROUP BY crId) AS mx ON pa.sal_co_or_id = crId AND pa.sal_pr_oc_id = ccId; -- 1、当前集合的(owCode + 有效状态 + 不是当前集合)更新为无效 UPDATE sal_co_orware SET ow_valid = 0 WHERE ow_code IN (SELECT ow_code FROM tmp_ocow); UPDATE sal_co_payment SET pm_valid = 0 WHERE pm_code IN (SELECT pm_code FROM tmp_ocpa); -- 2、当前集合更新为有效 UPDATE sal_co_orware AS wa JOIN tmp_ocow AS t ON t.ccId = wa.sal_pr_oc_id AND t.crId = wa.sal_co_or_id SET wa.ow_valid = 1; UPDATE sal_co_payment AS pa JOIN tmp_ocpa AS t ON t.ccId = pa.sal_pr_oc_id AND t.crId = pa.sal_co_or_id SET pa.pm_valid = 1; -- 3、删除临时创建的表 DROP TABLE tmp_ocow; DROP TABLE tmp_ocpa; -- 发送一条默认通过的用印申请 INSERT INTO ope_se_affix SELECT NULL AS id, (SELECT CONCAT(sc_prifix, sc_year, sc_month, LPAD(sc_num + 1, 5, '0')) FROM sys_co_servcode WHERE sc_serv_ident = 'SN080101' AND sc_prifix = 'YY' AND sc_year = SUBSTRING(YEAR(NOW()), 3) AND sc_month = MONTH(NOW())) AS af_code, orco.sys_ar_co_id AS af_seal_co_id, REPLACE(REPLACE(REPLACE(REPLACE(orco.or_affix_json ->> '$.afSealTypes', '[', ''), ']', ''), '"', ''), ' ', '') AS af_seal_type, 'B230001' AS af_paper_cate, orco.sal_pr_in_id AS sal_pr_in_id, orco.or_affix_json ->> '$.afPaperNum' AS af_paper_num, 0 AS af_num, '无' AS af_cause, 0 AS af_attch_num, orco.or_co_deadline AS af_deadline, pi.in_director AS af_proposer, pi.sys_ar_de_id AS sys_ar_de_id, orco.sys_ar_co_id AS sys_ar_co_id, ta.id AS sysCoAtId, orco.ccTime AS af_apply_time, '1' AS af_audit_state, '1' AS af_origin_type, 'SN030602' AS af_serv_ident, orco.ccId AS af_serv_id, '0' AS af_state, NULL AS af_person, NULL AS af_time, orco.ccTime AS create_time, orco.creator AS creator FROM (SELECT cc.id AS ccId, cc.oc_name AS ocName, cc.create_time AS ccTime, cr.* FROM sal_co_order AS cr JOIN sal_co_orchange AS cc ON cr.id = cc.sal_co_or_id) AS orco JOIN sal_pr_info AS pi ON orco.sal_pr_in_id = pi.id JOIN sys_co_apprtask AS ta ON ta.at_serv_id = orco.ccId AND ta.at_serv_ident = 'SN030602' WHERE orco.or_co_code = 'XS23110001' AND orco.ocName = '第一次变更' ORDER BY ta.create_time DESC LIMIT 1; -- 更新用印的系统编码维护记录 UPDATE sys_co_servcode SET sc_num = sc_num + 1 WHERE sc_serv_ident = 'SN080101' AND sc_prifix = 'YY' AND sc_year = SUBSTRING(YEAR(NOW()), 3) AND sc_month = MONTH(NOW());
标签:co,sal,cc,SQL,--,线上,MySQL,cr,id From: https://www.cnblogs.com/mindzone/p/17867275.html