使用来自另一个表的 COUNT/MIN/MAX 的 mySQL 嵌套更新

Question

我有两个大表，products(500k 条记录)和 store_products(> 3mm 条记录)。 Products 是主数据，product_stores 是产品的各个位置。

我需要运行一个 QUERY 来汇总来自 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)
);

要更新的字段:

• 存储 [store_product 记录的数量(按 productid)]
• 最低价格 [按 productid 计算的最低价格]
• 最高价格 [按 productid 计算的最高价格]

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);

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

希望这对您有所帮助!