【MySQL】 线上补数据SQL

昨天一下午就为了补业务数据，写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());