【MySQL】仓储--维护出入库流水、库存，去重&数量逻辑修正_数据库库存结存维护

系列文章

C#底层库–MySQLBuilder脚本构建类（select、insert、update、in、带条件的SQL自动生成）
本文链接：https://blog.csdn.net/youcheng_ge/article/details/129179216

C#底层库–MySQL数据库操作辅助类（推荐阅读）
本文链接：https://blog.csdn.net/youcheng_ge/article/details/126886379

C#底层库–SQLite的使用（小型、本地数据库）
本文链接：https://blog.csdn.net/youcheng_ge/article/details/123666958

【提高编程效率】Excel数据批量导入数据库
本文链接：https://blog.csdn.net/youcheng_ge/article/details/126427323

【Oracle】数据库服务器限制ip访问
本文链接：https://blog.csdn.net/youcheng_ge/article/details/122220930

【Oracle】Excel导入数据教程
本文链接：https://blog.csdn.net/youcheng_ge/article/details/118722756

【Oracle】数据库还原教程_数据泵
本文链接：https://blog.csdn.net/youcheng_ge/article/details/118054855

【SQL】如何查询表字段并识别主键
本文链接：https://blog.csdn.net/youcheng_ge/article/details/110820405

【SQL】outer apply的用法
本文链接：https://blog.csdn.net/youcheng_ge/article/details/79903489

【SQL】多表连接重复数据处理
本文链接：https://blog.csdn.net/youcheng_ge/article/details/79903619

MySQL安装教程（详细）
本文链接：https://blog.csdn.net/youcheng_ge/article/details/126037520

MySQL卸载教程（详细）
本文链接：https://blog.csdn.net/youcheng_ge/article/details/129279265

【MySQL】group by分类汇总，如何增加“总计”字段?
本文链接：https://blog.csdn.net/youcheng_ge/article/details/128217837

【MySQL】WITH CHECK OPTION的用法
本文链接：https://blog.csdn.net/youcheng_ge/article/details/128147196

【MySQL】使用存储过程插入千万级数据如何提升效率？
本文链接：https://blog.csdn.net/youcheng_ge/article/details/77728189

【MySQL】数据库表行列转置的实现
本文链接：https://blog.csdn.net/youcheng_ge/article/details/77625052

【MySQL】查询中，NULL值转换为空字符串
本文链接：https://blog.csdn.net/youcheng_ge/article/details/130381878

前言

本专栏为【数据库】，主要介绍SQL的功能与特点、SQL数据定义语言（表、视图、索引、约束）、SQL数据操作语言（数据检索、数据插入、数据删除、数据更新）、创建与删除触发器、SQL数据控制语言（安全性和授权、事务处理）以及嵌入式SQL。
如果你对本专辑感兴趣，持续关注吧。大家有任何问题，也可以评论区反馈，私信我。

一、技术介绍

采用备份表的方式，复制空的备份表，将重复纪录取出来，去重完后，插入备份表，删除原表中的所有重复纪录，然后将备份表记录（不重复的纪录）再重新插入 原表。

二、数据准备

三、解决方案

3.1 20230803

-- 手动核实 出入库流水、以及产品信息表数据
-- 1、维护出库流水表
-- 出现出库流水记录存在，但是模具信息表不存在的情况，出库流水一律删除
-- 单号 TOM开头为模具领用

SELECT *
FROM 模具出入库表 a
LEFT JOIN 模具信息表 b ON a.模具编号=b.模具编号 
WHERE a.出入库单据编号 LIKE 'TOM%' AND a.出入库类型='材料领用出库'
AND b. 模具编号 IS NULL

-- 2、从备份表中，找到缺失的模具信息数据
-- 模具信息表，不存在
SELECT * 
FROM 模具信息表
WHERE 模具编号 ='YP2ACX230322699'

-- 模具信息表_原，存在
SELECT * 
FROM 模具信息表_原
WHERE 模具编号 ='YP2ACX230322699'


SELECT c.模具编号,c.模具出厂编号,c.模具供应商编号,c.* 
FROM 模具出入库表 a
LEFT JOIN 模具信息表 b ON a.模具编号=b.模具编号 
LEFT JOIN 模具信息表_原 c ON a.模具编号=c.模具编号 
WHERE a.出入库单据编号 LIKE 'TOM%' AND a.出入库类型='材料领用出库'
AND b.模具编号 IS NULL AND c.模具编号 IS NOT NULL

-- 3、验证2的合理性
-- 模具信息表，不存在
SELECT * 
FROM 模具信息表
WHERE 模具编号 ='YP2ACX230303306'

-- 模具信息表_原，存在
SELECT * 
FROM 模具信息表_原
WHERE 模具编号 ='YP2ACX230303306'

-- 模具信息表，不存在
SELECT * 
FROM 模具信息表
WHERE 模具编号 ='HP1AAXJC20230893'

-- 模具信息表_原，存在
SELECT * 
FROM 模具信息表_原
WHERE 模具编号 ='HP1AAXJC20230893'


-- 4、增补模具信息表缺失的数据
INSERT INTO `dl_mes`.`模具信息表` (`公司编号`, `工厂名称`, `存货编码`, `采购订单编号`, `领用申请单编号`, `销售订单编号`, `模具供应商编号`, `模具类别`, `模具类型`, `模具材质`, `模具出厂编号`, `模具出厂规格`, `模具出厂直径`, `返修次数`, `模具编号`, `模链编号`, `模具标准规格`, `模具规格`, `模具标准直径`, `模具直径`, `模芯规格`, `粒度`, `状态描述`, `库存状态`, `仓库编号`, `货位编号`, `设备编号`, `下盘编号`, `下盘规格`, `累计公里数`, `累计盘数`, `行动`, `模具打标时间`, `模具打标录入时间`, `模具打标操作人编号`, `模具打标备注`, `模具检验时间`, `模具检验录入时间`, `模具检验人编号`, `异常类型`, `特殊检验说明`, `模具检验备注`, `最小直径`, `最大直径`, `平均直径`, `椭圆度`, `椭圆率`, `左定径长度`, `右定径长度`, `定径长度`, `有效值`, `定径区AR`, `左压缩角`, `右压缩角`, `压缩角`, `压缩区AR1`, `压缩区AR2`, `孔偏角X`, `孔偏角Y`, `倍率`, `检验结果`, `模具首次入库时间`, `领用类型`, `模具领用时间`, `模具领用录入时间`, `模具领用人编号`, `模具领用备注`, `模具退库时间`, `模具退库录入时间`, `模具退库操作人编号`, `模具退库原因`, `模具退库备注`, `模具采购退货时间`, `模具采购退货录入时间`, `模具采购退货操作人编号`, `模具采购退货原因`, `模具采购退货备注`, `模具报废时间`, `模具报废录入时间`, `模具报废操作人编号`, `模具报废原因`, `模具报废备注`) 

SELECT c.`公司编号`, c.`工厂名称`, c.`存货编码`, c.`采购订单编号`, c.`领用申请单编号`, c.`销售订单编号`, c.`模具供应商编号`, c.`模具类别`, c.`模具类型`, c.`模具材质`, c.`模具出厂编号`, c.`模具出厂规格`, c.`模具出厂直径`, c.`返修次数`, c.`模具编号`, c.`模链编号`, c.`模具标准规格`, c.`模具规格`, c.`模具标准直径`, c.`模具直径`, c.`模芯规格`, c.`粒度`, c.`状态描述`, c.`库存状态`, c.`仓库编号`, c.`货位编号`, c.`设备编号`, c.`下盘编号`, c.`下盘规格`, c.`累计公里数`, c.`累计盘数`, c.`行动`, c.`模具打标时间`, c.`模具打标录入时间`, c.`模具打标操作人编号`, c.`模具打标备注`, c.`模具检验时间`, c.`模具检验录入时间`, c.`模具检验人编号`, c.`异常类型`, c.`特殊检验说明`, c.`模具检验备注`, c.`最小直径`, c.`最大直径`, c.`平均直径`, c.`椭圆度`, c.`椭圆率`, c.`左定径长度`, c.`右定径长度`, c.`定径长度`, c.`有效值`, c.`定径区AR`, c.`左压缩角`, c.`右压缩角`, c.`压缩角`, c.`压缩区AR1`, c.`压缩区AR2`, c.`孔偏角X`, c.`孔偏角Y`, c.`倍率`, c.`检验结果`, c.`模具首次入库时间`, c.`领用类型`, c.`模具领用时间`, c.`模具领用录入时间`, c.`模具领用人编号`, c.`模具领用备注`, c.`模具退库时间`, c.`模具退库录入时间`, c.`模具退库操作人编号`, c.`模具退库原因`, c.`模具退库备注`, c.`模具采购退货时间`, c.`模具采购退货录入时间`, c.`模具采购退货操作人编号`, c.`模具采购退货原因`, c.`模具采购退货备注`, c.`模具报废时间`, c.`模具报废录入时间`, c.`模具报废操作人编号`, c.`模具报废原因`, c.`模具报废备注` 
FROM 模具出入库表 a
LEFT JOIN 模具信息表 b ON a.模具编号=b.模具编号 
LEFT JOIN 模具信息表_原 c ON a.模具编号=c.模具编号 
WHERE a.出入库单据编号 LIKE 'TOM%' AND a.出入库类型='材料领用出库'
AND b.模具编号 IS NULL AND c.模具编号 IS NOT NULL

-- 5、维护入库流水表
-- 有出库流水数据，必然有入库流水，查找入库流水缺失情况
-- 模具出库流水
SELECT
*
FROM 模具出入库表 a
WHERE a.出入库单据编号 LIKE 'TOM%' AND a.出入库类型='材料领用出库'
-- 模具入库流水
SELECT
*
FROM 模具出入库表 b 
WHERE b.出入库单据编号 LIKE 'BIM%' AND b.出入库类型='采购入库'


SELECT
a.*
FROM

(
SELECT * 
FROM 模具出入库表 a
WHERE a.出入库单据编号 LIKE 'TOM%' AND a.出入库类型='材料领用出库'
) a
LEFT JOIN
(
SELECT *
FROM 模具出入库表 b 
WHERE b.出入库单据编号 LIKE 'BIM%' AND b.出入库类型='采购入库'
) b ON a.模具编号 = b.模具编号

WHERE b.模具编号 IS NULL

模具入库流水，缺2条数据，模具编号如下：
DQP1BAX230506101
DQP1AAXD30627054

-- 6、补齐入库流水数据
SELECT *
FROM 模具出入库表_原 b 
WHERE 
-- b.出入库单据编号 LIKE 'BIM%' AND 
b.出入库类型='采购入库'
AND b.模具编号 IN('DQP1BAX230506101','DQP1AAXD30627054')


INSERT INTO `dl_mes`.`模具出入库表` (`公司编号`, `采购订单编号`, `产成品入库单编号`, `领料申请单编号`, `销售订单编号`, `出入库单据编号`, `出入库类型`, `存货编码`, `仓库编号`, `货位编号`, `模具编号`, `数量`, `出入库时间`, `出入库录入时间`, `出入库操作人编号`, `库管员编号`, `出入库备注`) VALUES ('Z', '', '空', '空', '空', 'BIM202307049', '采购入库', '03010114', '09', '09', 'DQP1AAXD30627054', 1.000, '2023-07-30 20:02:21', '2023-07-30 20:02:23', 'developer', 'developer', '模具入库');
INSERT INTO `dl_mes`.`模具出入库表` (`公司编号`, `采购订单编号`, `产成品入库单编号`, `领料申请单编号`, `销售订单编号`, `出入库单据编号`, `出入库类型`, `存货编码`, `仓库编号`, `货位编号`, `模具编号`, `数量`, `出入库时间`, `出入库录入时间`, `出入库操作人编号`, `库管员编号`, `出入库备注`) VALUES ('Z', '', '空', '空', '空', 'BIM202307052', '采购入库', '03010114', '09', '09', 'DQP1BAX230506101', 1.000, '2023-07-31 17:41:28', '2023-07-31 17:41:28', 'developer', 'developer', '模具入库');

-- 7、检验 出库流水、入库流水是否有对应
-- 有出库，必然有入库
-- 有入库，不一定有出库
-- 结存数量+出库数=入库数量
SELECT
a.*
FROM

(
SELECT * 
FROM 模具出入库表 a
WHERE a.出入库单据编号 LIKE 'TOM%' AND a.出入库类型='材料领用出库'
) a
LEFT JOIN
(
SELECT *
FROM 模具出入库表 b 
WHERE b.出入库单据编号 LIKE 'BIM%' AND b.出入库类型='采购入库'
) b ON a.模具编号 = b.模具编号

WHERE b.模具编号 IS NULL

结论：无数据，证明出入库流水正确。

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
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134

3.2 20230804

-- 1、不该入库的产生入库流水，模具编号还是空的，不应该入库
SELECT * 
FROM 模具出入库表
WHERE 模具编号='空'

DELETE FROM 模具出入库表 WHERE 模具编号='空'


-- 2、入库流水重复的数据，模具编号、出入库录入时间，完全一致的数据可判断为重复纪录
SELECT 模具出入库表.模具编号,COUNT(模具编号) 
FROM 模具出入库表
WHERE 出入库类型='采购入库'
GROUP BY 出入库单据编号,模具编号,出入库录入时间 HAVING COUNT(模具编号)>1;

-- 606条


-- 3、入库流水重复的数据，去重后的记录，保存进 模具出入库表_copy1
INSERT INTO `dl_mes`.`模具出入库表_copy1` (`公司编号`, `采购订单编号`, `产成品入库单编号`, `领料申请单编号`, `销售订单编号`, `出入库单据编号`, `出入库类型`, `存货编码`, `仓库编号`, `货位编号`, `模具编号`, `数量`, `出入库时间`, `出入库录入时间`, `出入库操作人编号`, `库管员编号`, `出入库备注`)
SELECT `公司编号`, `采购订单编号`, `产成品入库单编号`, `领料申请单编号`, `销售订单编号`, `出入库单据编号`, `出入库类型`, `存货编码`, `仓库编号`, `货位编号`, `模具编号`, `数量`, `出入库时间`, `出入库录入时间`, `出入库操作人编号`, `库管员编号`, `出入库备注`
FROM 模具出入库表
WHERE 出入库类型='采购入库'
GROUP BY 出入库单据编号,模具编号,出入库录入时间 HAVING COUNT(模具编号)>1;

-- 606条


-- 4、查询 模具出入库表，存在重复的数据，全部删除
SELECT a.* 
FROM 模具出入库表 a
INNER JOIN 模具出入库表_copy1 b 
ON a.出入库单据编号=b.出入库单据编号 
AND a.模具编号=b.模具编号
AND a.出入库录入时间=b.出入库录入时间
WHERE a.出入库类型='采购入库'

-- 1228条


-- 5、步骤4的数据，全部删除（无法执行）
DELETE FROM 模具出入库表 a
INNER JOIN 模具出入库表_copy1 b 
ON a.出入库单据编号=b.出入库单据编号 
AND a.模具编号=b.模具编号
AND a.出入库录入时间=b.出入库录入时间
WHERE a.出入库类型='采购入库'


-- 6、重新插入不重复的606条，即步骤3的数据
INSERT INTO `dl_mes`.`模具出入库表` (`公司编号`, `采购订单编号`, `产成品入库单编号`, `领料申请单编号`, `销售订单编号`, `出入库单据编号`, `出入库类型`, `存货编码`, `仓库编号`, `货位编号`, `模具编号`, `数量`, `出入库时间`, `出入库录入时间`, `出入库操作人编号`, `库管员编号`, `出入库备注`)
SELECT `公司编号`, `采购订单编号`, `产成品入库单编号`, `领料申请单编号`, `销售订单编号`, `出入库单据编号`, `出入库类型`, `存货编码`, `仓库编号`, `货位编号`, `模具编号`, `数量`, `出入库时间`, `出入库录入时间`, `出入库操作人编号`, `库管员编号`, `出入库备注`
FROM 模具出入库表_copy1


-- 7、出库流水重复的数据，模具编号、出入库录入时间，完全一致的数据可判断为重复纪录
SELECT 模具出入库表.模具编号,COUNT(模具编号) 
FROM 模具出入库表
WHERE 出入库类型='材料领用出库'
GROUP BY 出入库单据编号,模具编号,出入库录入时间 HAVING COUNT(模具编号)>1;

-- 605条


-- 8、出库流水重复的数据，去重后的记录，保存进 模具出入库表_copy1
INSERT INTO `dl_mes`.`模具出入库表_copy1` (`公司编号`, `采购订单编号`, `产成品入库单编号`, `领料申请单编号`, `销售订单编号`, `出入库单据编号`, `出入库类型`, `存货编码`, `仓库编号`, `货位编号`, `模具编号`, `数量`, `出入库时间`, `出入库录入时间`, `出入库操作人编号`, `库管员编号`, `出入库备注`)
SELECT `公司编号`, `采购订单编号`, `产成品入库单编号`, `领料申请单编号`, `销售订单编号`, `出入库单据编号`, `出入库类型`, `存货编码`, `仓库编号`, `货位编号`, `模具编号`, `数量`, `出入库时间`, `出入库录入时间`, `出入库操作人编号`, `库管员编号`, `出入库备注`
FROM 模具出入库表
WHERE 出入库类型='材料领用出库'
GROUP BY 出入库单据编号,模具编号,出入库录入时间 HAVING COUNT(模具编号)>1;


-- 9、查询 模具出入库表，存在重复的数据，全部删除
SELECT a.* 
FROM 模具出入库表 a
INNER JOIN 模具出入库表_copy1 b 
ON a.出入库单据编号=b.出入库单据编号 
AND a.模具编号=b.模具编号
AND a.出入库录入时间=b.出入库录入时间
WHERE a.出入库类型='材料领用出库'

-- 1226条

-- 10、重新插入不重复的605条，即步骤3的数据
INSERT INTO `dl_mes`.`模具出入库表` (`公司编号`, `采购订单编号`, `产成品入库单编号`, `领料申请单编号`, `销售订单编号`, `出入库单据编号`, `出入库类型`, `存货编码`, `仓库编号`, `货位编号`, `模具编号`, `数量`, `出入库时间`, `出入库录入时间`, `出入库操作人编号`, `库管员编号`, `出入库备注`)
SELECT `公司编号`, `采购订单编号`, `产成品入库单编号`, `领料申请单编号`, `销售订单编号`, `出入库单据编号`, `出入库类型`, `存货编码`, `仓库编号`, `货位编号`, `模具编号`, `数量`, `出入库时间`, `出入库录入时间`, `出入库操作人编号`, `库管员编号`, `出入库备注`
FROM 模具出入库表_copy1


-- 11、物料出入库 与 模具入库流水，库存数对不上的处理
SELECT 出入库单据编号,存货编码,SUM(数量) AS '数量',MAX(出入库时间),MAX(出入库录入时间)
FROM 模具出入库表
WHERE 出入库类型='采购入库'
GROUP BY 出入库单据编号,存货编码
-- 84条

-- 将84条数据备份，转储至 物料出入库表_copy1
INSERT INTO `dl_mes`.`物料出入库表_copy1` (`公司编号`, `采购订单编号`, `产成品入库单编号`, `领料申请单编号`, `销售订单编号`, `出入库类型`, `存货编码`, `仓库编号`, `货位编号`, `数量`, `出入库时间`, `出入库录入时间`, `出入库操作人编号`, `库管员编号`, `出入库备注`)

SELECT 公司编号,采购订单编号,产成品入库单编号,领料申请单编号,销售订单编号,
出入库类型,存货编码,仓库编号, 货位编号,SUM(数量) AS '数量',
MAX(出入库时间) AS '出入库时间',MAX(出入库录入时间) AS '出入库录入时间',
出入库操作人编号, 库管员编号, 出入库备注
FROM 模具出入库表
WHERE 出入库类型='采购入库'
GROUP BY 出入库单据编号,存货编码

-- 12、删除 物料出入库中，模具入库的记录，重新导入84条数据备份
SELECT * FROM 物料出入库表 WHERE 出入库备注='模具入库'
DELETE FROM 物料出入库表 WHERE 出入库备注='模具入库'

INSERT INTO `dl_mes`.`物料出入库表` ( `公司编号`, `采购订单编号`, `产成品入库单编号`, `领料申请单编号`, `销售订单编号`, `出入库类型`, `存货编码`, `仓库编号`, `货位编号`, `数量`, `出入库时间`, `出入库录入时间`, `出入库操作人编号`, `库管员编号`, `出入库备注`) 
SELECT `公司编号`, `采购订单编号`, `产成品入库单编号`, `领料申请单编号`, `销售订单编号`, `出入库类型`, `存货编码`, `仓库编号`, `货位编号`, `数量`, `出入库时间`, `出入库录入时间`, `出入库操作人编号`, `库管员编号`, `出入库备注`
FROM 物料出入库表_copy1


-- 13、物料出入库 与 模具入库流水，库存数对不上的处理
SELECT 出入库单据编号,存货编码,SUM(数量) AS '数量',MAX(出入库时间),MAX(出入库录入时间)
FROM 模具出入库表
WHERE 出入库类型='材料领用出库'
GROUP BY 出入库单据编号,存货编码
-- 153条

-- 将 153条数据备份，转储至 物料出入库表_copy1
INSERT INTO `dl_mes`.`物料出入库表_copy1` (`公司编号`, `采购订单编号`, `产成品入库单编号`, `领料申请单编号`, `销售订单编号`, `出入库类型`, `存货编码`, `仓库编号`, `货位编号`, `数量`, `出入库时间`, `出入库录入时间`, `出入库操作人编号`, `库管员编号`, `出入库备注`)

SELECT 公司编号,采购订单编号,产成品入库单编号,领料申请单编号,销售订单编号,
出入库类型,存货编码,仓库编号, 货位编号,SUM(数量) AS '数量',
MAX(出入库时间) AS '出入库时间',MAX(出入库录入时间) AS '出入库录入时间',
出入库操作人编号, 库管员编号, 出入库备注
FROM 模具出入库表
WHERE 出入库类型='材料领用出库'
GROUP BY 出入库单据编号,存货编码


-- 14、删除 物料出入库中，模具出库的记录，重新导入 153条数据备份
SELECT * FROM 物料出入库表 WHERE 出入库备注='模具领用出库'
DELETE FROM 物料出入库表 WHERE 出入库备注='模具领用出库'

INSERT INTO `dl_mes`.`物料出入库表` ( `公司编号`, `采购订单编号`, `产成品入库单编号`, `领料申请单编号`, `销售订单编号`, `出入库类型`, `存货编码`, `仓库编号`, `货位编号`, `数量`, `出入库时间`, `出入库录入时间`, `出入库操作人编号`, `库管员编号`, `出入库备注`) 
SELECT `公司编号`, `采购订单编号`, `产成品入库单编号`, `领料申请单编号`, `销售订单编号`, `出入库类型`, `存货编码`, `仓库编号`, `货位编号`, `数量`, `出入库时间`, `出入库录入时间`, `出入库操作人编号`, `库管员编号`, `出入库备注`
FROM 物料出入库表_copy1


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
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143

四、用法总结

数据去重
入库流水重复的数据
出库流水重复的数据