【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());

  

posted @   emdzz  阅读(29)  评论(0编辑  收藏  举报
(评论功能已被禁用)
相关博文:
阅读排行:
· 被坑几百块钱后,我竟然真的恢复了删除的微信聊天记录!
· 没有Manus邀请码?试试免邀请码的MGX或者开源的OpenManus吧
· 【自荐】一款简洁、开源的在线白板工具 Drawnix
· 园子的第一款AI主题卫衣上架——"HELLO! HOW CAN I ASSIST YOU TODAY
· Docker 太简单,K8s 太复杂?w7panel 让容器管理更轻松!
历史上的今天:
2021-11-30 【SpringBoot】数据源加密处理
2020-11-30 【Linux】Re04
点击右上角即可分享
微信分享提示