【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()); |
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 被坑几百块钱后,我竟然真的恢复了删除的微信聊天记录!
· 没有Manus邀请码?试试免邀请码的MGX或者开源的OpenManus吧
· 【自荐】一款简洁、开源的在线白板工具 Drawnix
· 园子的第一款AI主题卫衣上架——"HELLO! HOW CAN I ASSIST YOU TODAY
· Docker 太简单,K8s 太复杂?w7panel 让容器管理更轻松!
2021-11-30 【SpringBoot】数据源加密处理
2020-11-30 【Linux】Re04