【MySQL】 线上补数据SQL

昨天一下午就为了补业务数据，写SQL快崩溃了得...

直接贴不解释了

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100

-- 先拿到[销售变更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());