时间戳

首页 > 解决方案 > 更新和增加重复的多列

mysql - 更新和增加重复的多列

问题描述

我有以下查询来插入多个值，并且重复时需要更新列

 INSERT INTO stock (price, stock1, stock2) VALUES
  ('99', '10', 0), 
  ('120', 0, '10')
ON DUPLICATE KEY UPDATE  
  stock1 = COALESCE(stock1 + VALUES(stock1), stock1), 
  `stock1-C` = `stock1-C` + (VALUES(stock1) IS NOT NULL),
  stock2 = COALESCE(stock2 + VALUES(stock2), stock2), 
  `stock2-C` = `stock2-C` + (VALUES(stock2) IS NOT NULL)

第一次运行这个查询应该用新值添加 (+)列现有值并用 1 增加现有列-C值，让我尝试用一​​些例子来解释

以下是默认表，其中price是唯一列，其余列的默认值为 0

价格 库存1 库存1-C 库存2 库存2-C

运行以下值会创建这样的行

('99', '10', 0), 
('120', 0, '12')
价格 库存1 库存1-C 库存2 库存2-C
99 10 0 0 0
120 0 0 12 0

再次像这样运行相同的值应该添加具有新值的列并增加列-C列

('99', '10', 0), 
('120', 0, '12')
价格 库存1 库存1-C 库存2 库存2-C
99 20 1 0 0
120 0 0 24 1

预期结果 ：

以上两个查询非常适合我的代码，现在让我们尝试插入重复价格

('150', '55', 0), 
  ('150', 0, '56')

因为在它应该给出之前没有 150 的价格：

价格 库存1 库存1-C 库存2 库存2-C
99 20 1 0 0
120 0 0 24 1
150 55 0 56 0

再次运行应该更新如下行：

 ('150', '10', 0), 
 ('150', 0, '10')
价格 库存1 库存1-C 库存2 库存2-C
99 20 1 0 0
120 0 0 24 1
150 65 1 66 1

实际结果 ：

但是第一次用 1 更新行，下一次用 3 更新

('150', '55', 0), ('150', 0, '56')

价格 库存1 库存1-C 库存2 库存2-C
99 20 1 0 0
120 0 0 24 1
150 55 1 56 1

我第二次跑步时会这样

('150', '55', 0), ('150', 0, '56')

价格 库存1 库存1-C 库存2 库存2-C
99 20 1 0 0
120 0 0 24 1
150 55 3 56 3

而如果我在多个查询中这样做，它工作正常

INSERT INTO `bankvolume` (`price`, `stock1`) VALUES (100, 10) ON DUPLICATE KEY UPDATE `stock1` = COALESCE(`stock1` + VALUES(`stock1`), `stock1`), 
  `stock1-C` = `stock1-C` + (VALUES(`stock1`) IS NOT NULL);


INSERT INTO `bankvolume` (`price`, `stock2`) VALUES (100, 10) ON DUPLICATE KEY UPDATE `stock2` = COALESCE(`stock2` + VALUES(`stock2`), `stock2`), 
  `stock2-C` = `stock2-C` + (VALUES(`stock2`) IS NOT NULL);

我想将两者合并为一个，

Ps : 我有 50 多只股票要在单个查询中更新，这就是为什么要尝试优化我的代码，谢谢。

标签： mysqlsql

解决方案

由于列不可为空，因此您必须为不想在语句中提供值的列传递0s 而不是s，并使用比较 to而不是：nullINSERT0null

INSERT INTO stock (price, stock1, stock2) VALUES
  ('99', '10', 0), 
  ('120', 0, '12')
ON DUPLICATE KEY UPDATE 
  `stock1-C` = `stock1-C` + (VALUES(stock1) <> 0 AND stock1 <> 0),
  stock1 = stock1 + VALUES(stock1), 
  `stock2-C` = `stock2-C` + (VALUES(stock2) <> 0 AND stock2 <> 0),
  stock2 = stock2 + VALUES(stock2)

结果：

价格 库存1 库存1-C 库存2 库存2-C
99 10 0 0 0
120 0 0 12 0

再说一遍：

INSERT INTO stock (price, stock1, stock2) VALUES
  ('99', '10', 0), 
  ('120', 0, '12')
ON DUPLICATE KEY UPDATE 
  `stock1-C` = `stock1-C` + (VALUES(stock1) <> 0 AND stock1 <> 0),
  stock1 = stock1 + VALUES(stock1), 
  `stock2-C` = `stock2-C` + (VALUES(stock2) <> 0 AND stock2 <> 0),
  stock2 = stock2 + VALUES(stock2)

结果：

价格 库存1 库存1-C 库存2 库存2-C
99 20 1 0 0
120 0 0 24 1

然后：

INSERT INTO stock (price, stock1, stock2) VALUES
  ('150', '55', 0), 
  ('150', 0, '56')
ON DUPLICATE KEY UPDATE 
  `stock1-C` = `stock1-C` + (VALUES(stock1) <> 0 AND stock1 <> 0),
  stock1 = stock1 + VALUES(stock1), 
  `stock2-C` = `stock2-C` + (VALUES(stock2) <> 0 AND stock2 <> 0),
  stock2 = stock2 + VALUES(stock2)

结果：

价格 库存1 库存1-C 库存2 库存2-C
99 20 1 0 0
120 0 0 24 1
150 55 0 56 0

然后：

INSERT INTO stock (price, stock1, stock2) VALUES
  ('150', '10', 0), 
  ('150', 0, '10')
ON DUPLICATE KEY UPDATE 
  `stock1-C` = `stock1-C` + (VALUES(stock1) <> 0 AND stock1 <> 0),
  stock1 = stock1 + VALUES(stock1), 
  `stock2-C` = `stock2-C` + (VALUES(stock2) <> 0 AND stock2 <> 0),
  stock2 = stock2 + VALUES(stock2)

结果：

价格 库存1 库存1-C 库存2 库存2-C
99 20 1 0 0
120 0 0 24 1
150 65 1 66 1

请参阅演示。

推荐阅读