MySQL:使用另一个表的计算更新所有记录

Question

我需要使用 2 列(mixedPoints 和 ProductQuantity)的总和进行计算，其中行属于一个表 (tblSubmissionProducts) 上的客户，然后将该值存储在客户表 (tblCustomer) 上的客户记录中.

1. 需要为每个客户(10,000 条记录)更新此信息。
2. 每个客户在 tblSubmissionProducts 中可能有 0 行或多行。
3. 仅计算 mixPoints 值 > 0 的情况。

我为一位客户创建了一个查询，但它似乎不适用于整个表

SET @custID = '9';
SELECT customerID, 

(SELECT ROUND(SUM(mixedPointsTotal) / SUM(productQuantity), 2) AS 'Avg Mixed Points' 
FROM tblSubmissionProducts 
WHERE tblSubmissionProducts.customerID = @custID),

(SELECT ROUND(SUM(mixedPointsTotal) / SUM(productQuantity), 2) AS '3mth Mixed Points' 
FROM tblSubmissionProducts 
WHERE tblSubmissionProducts.customerID = @custID
AND submissionProductDate BETWEEN DATE_SUB(NOW(), INTERVAL 3 MONTH) AND NOW()),

(SELECT ROUND(SUM(mixedPointsTotal) / SUM(productQuantity), 2) AS '6mth Mixed Points' 
FROM tblSubmissionProducts 
WHERE tblSubmissionProducts.customerID = @custID
AND submissionProductDate BETWEEN DATE_SUB(NOW(), INTERVAL 6 MONTH) AND NOW())

;

这是完整列表查询的第一部分:它似乎只是锁定表太长时间并且只更新一小部分记录。我最终不得不取消查询。

如有任何帮助，我们将不胜感激。

UPDATE tblCustomer, tblSubmissionProducts
SET  tblCustomer.mixedPoints = 
(SELECT ROUND(SUM(tblSubmissionProducts.mixedPointsTotal) / SUM(tblSubmissionProducts.productQuantity), 2) 
FROM tblSubmissionProducts WHERE tblSubmissionProducts.customerID = tblCustomer.CustomerID
AND tblSubmissionProducts.mixedPoints > 0);

提前非常感谢。

Answer 1

使用 MySQL update-join 构造，例如

UPDATE tblCustomer
JOIN (SELECT customerID,
ROUND(SUM(tblSubmissionProducts.mixedPointsTotal) / SUM(tblSubmissionProducts.productQuantity), 2) as colx
FROM tblSubmissionProducts 
JOIN tblCustomer ON tblSubmissionProducts.customerID = tblCustomer.CustomerID
WHERE tblSubmissionProducts.mixedPoints > 0 ) xxx

ON xxx.customerID = tblCustomer.CustomerID
SET  tblCustomer.mixedPoints = xxx.colx;