首页 > 数据库 >【MySQL】 线上补数据SQL

【MySQL】 线上补数据SQL

时间:2023-11-30 14:33:56浏览次数:43  
标签:co sal cc SQL -- 线上 MySQL cr id

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

相关文章

  • 数据库系列:MySQL InnoDB锁机制介绍
    数据库系列:MySQL慢查询分析和性能优化数据库系列:MySQL索引优化总结(综合版)数据库系列:高并发下的数据字段变更数据库系列:覆盖索引和规避回表数据库系列:数据库高可用及无损扩容数据库系列:使用高区分度索引列提升性能数据库系列:前缀索引和索引长度的取舍数据库系列:MySQL引擎My......
  • MySQL8.0存储引擎对比
    MySQL8.0存储引擎对比MySQL8默认支持的存储引擎有MEMORY,PERFORMANCE_SCHEMA,MyISAM,MRG_MYISAM,BLACKHOLE,CSV,ARCHIVE和InnoDB共8种存储引擎。其中InnoDB为MySQL8的默认存储引擎。存储引擎信息在数据库中的存储位置为:INFORMATION_SCHEMA下的ENGINES表。ENGINESUPPORTCOMMENT......
  • SQL 关键字执行顺序
    1.FromandJOIN首先我们进行查询的时候,肯定是先获得一份数据集的,From语句和JOIN被先执行的,就是为了获得数据集的。2.WHERE一旦数据集给拿到了,WHERE限制条件会被用到某些行上,并把不满足的行给抛弃掉。并且,这能够用到各种数据类型中3.GROUPBYWHERE条件限定之后,接下来就是GRO......
  • 【必读】开发者必备!轻松学习MySQL事务的使用方法
    在日常开发中我们经常会遇到需要同时处理多个操作的情况,比如在购物时,我们需要同时完成支付和更新库存两个操作。这时,如果其中一个操作失败了,我们就需要进行回滚,以保证数据的一致性。那么,如何在MySQL中实现这样的功能呢?答案就是——事务。下面我们就来介绍一下MySQL事务是什么?它......
  • 翻译:MySQL InnoDB Cluster - Navigating the Cluster
    本文是对这篇文章MySQLInnoDBCluster-NavigatingtheCluster[1]的翻译,翻译如有不当的地方,敬请谅解,请尊重原创和翻译劳动成果,转载的时候请注明出处。谢谢!当我们管理InnoDBCluster时,一件非常重要的事情就是了解集群处于什么样的状态,特别是要了解如何解释集群状态的报告,以及如何......
  • 部分MySQL的SQL信息整理
    模块补丁信息查看selectsuas补丁模块,count(1)as数量fromgsppatchlogwhereTO_DAYS(NOW())-TO_DAYS(deployedtime)<=300groupbysuorderby2descselectpatchcodefromgsppatchlogwhereTO_DAYS(NOW())-TO_DAYS(deployedtime)<=1orderby1......
  • CTP行情实时写入MySQL数据库
    CTP行情实时写入MySQL数据库 CTP即中国期货市场的交易系统,是一种可以实时获取行情数据的开放式软件平台。在这个平台上,我们可以订阅多种不同的市场行情数据,例如期货、股票、基金等等。而将这些大量的数据存入数据库,可以为量化交易策略提供必要的支持。 在这篇文章中,我们......
  • 不同数据库创建用户,数据库的SQL语句整理
    不同数据库创建用户,数据库的SQL语句整理MySQLmysql-uroot-p#输入密码登录数据库CREATEDATABASEIFNOTEXISTSxxxdata_someinfoDEFAULTCHARSETutf8mb4;createuser'xxx_someinfo'@'%'identifiedby'Testsomepassword';grantallprivilegesonxxxdata_......
  • OpenCASCADE曲线上点的反求
    本文主要结合代码介绍OpenCASCADE曲线上点的反求实现原理及使用过程中的一些注意事项。OpenCASCADE曲线上点的反求1Introduction曲线可以用代数方程表示,如圆可以用X^2+Y^2=R^2表示,也可以用参数方程X(u)=RCos(u),Y(u)=RSin(u)表示。要判断点是不是在线上,用......
  • SQL Server中left join、inner join和right join的区别?
    数据库是我们IT行家常的事情,相信大家都不陌生,计算机专业都开了数据库系统概论这门课程,我分享下在暑假找实习参加宣讲会过程做的笔试题中遇到关于leftjoin、innerjoin和rightjoin的区别的简述题,希望对需要的朋友有所帮助。看下面一个小例子,我懒的开软件,用Excel替代下,谅解谅解~st......