使用另一个表中的 COUNT/MIN/MAX 进行 mySQL 嵌套更新

Question

我有两个大表，products（500k 记录）和 store_products（> 3mm 记录）。 Products 是主目录，product_stores 是产品的各个位置。

我需要运行一个查询来汇总来自product_stores 的信息并更新相应的产品。

当这是较小的数据集时，我们使用嵌套查询来完成：

SELECT productid,COUNT(id) as count,MIN(price) as lowprice,MAX(price) as highprice FROM store_products
WHILE (productid){ update product set stores = count, min = lowprice, max = highprice WHERE productid = $productid }
GROUP BY productid

我对嵌套更新相当陌生，并且不确定如何使用联接和分组依据设置多个字段。

结构 [截断为相关字段]：

CREATE TABLE product ( 
product_id INT UNSIGNED NOT NULL AUTO_INCREMENT,     
stores INT UNSIGNED NOT NULL DEFAULT '0',    
lowprice DECIMAL (6,2) NOT NULL DEFAULT '000.00', 
highprice  DECIMAL (6,2) NOT NULL DEFAULT '000.00', 
PRIMARY KEY (product_id), 
KEY stores (stores) 
)

CREATE TABLE store_product (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,    
product_id INT UNSIGNED NOT NULL,
price DECIMAL(7,2) NOT NULL DEFAULT '0.00',
PRIMARY KEY (storeproduct_id),
KEY product_id (product_id)
);

要更新的字段：

• 商店 [按产品 ID 划分的 store_product 记录数]
• 最低价格 [按产品 ID 划分的价格最小值]
• 最高价格 [按产品 ID 划分的价格最大值]

Answer 1

运行单个查询来对这种大小的表执行更新可能需要一段时间。无论如何 - 以下内容应该可以满足您的需求。技巧是为产品表设置别名，然后使用该别名在子查询中引用产品表。因此：

update product p 
set p.lowprice = (select min(price) from store_product sp where sp.product_id = p.product_id),
    p.highprice = (select max(price) from store_product sp where sp.product_id = p.product_id),
    p.stores = (select count(*) from store_product sp where sp.product_id = p.product_id)
where product_id in (select sp.product_id from store_product sp);

这里的一个问题是，对于 store_product 表中不存在的行，stores 列不会更新为 0。为了满足这一点，您可以使用 IFNULL 在执行全局更新时：

update product p
set lowprice = ifnull((select min(price) from store_product sp where sp.product_id = p.product_id),0),
    highprice = ifnull((select max(price) from store_product sp where sp.product_id = p.product_id),0),
    stores = ifnull((select count(*) from store_product sp where sp.product_id = p.product_id),0);

您可能想尝试两者，看看哪个更快。

希望这有帮助！