mysql - 在更新查询中使用从选择子查询返回的结果

Question

我有一个查询，需要更新表。这是我的选择查询:

SELECT  so.fk_customer,
IF (((sum( lgg.fk_catalog_attribute_option_global_gender = 1 )/count( lgg.fk_catalog_attribute_option_global_gender )) *100 > 60) AND (c.gender='male'), 'Men',
IF (((sum( lgg.fk_catalog_attribute_option_global_gender = 2 )/count( lgg.fk_catalog_attribute_option_global_gender )) *100 > 60) AND (c.gender='female'), 'Women', '') ) as calculatedGender
FROM catalog_attribute_link_global_gender AS lgg
INNER JOIN catalog_simple AS cs ON cs.fk_catalog_config = lgg.fk_catalog_config
INNER JOIN sales_order_item AS soi ON soi.sku = cs.sku
INNER JOIN sales_order AS so ON soi.fk_sales_order = so.id_sales_order 
INNER JOIN customer as c ON c.id_customer = so.fk_customer
WHERE lgg.fk_catalog_attribute_option_global_gender IN (1,2)
AND so.created_at BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) AND NOW()
GROUP BY so.fk_customer
HAVING count( lgg.fk_catalog_attribute_option_global_gender ) > 2

我有另一个表，其中有一列 fk_customer 和一列性别，我需要使用上述查询的结果更新该表。我需要在同一个查询中执行此操作。上面的查询给了我完美的结果。

Answer 1

您必须将 UPDATE 语句与此查询合并为:

UPDATE anotherTable A JOIN
(SELECT  so.fk_customer,
IF (((sum( lgg.fk_catalog_attribute_option_global_gender = 1 ) / count(lgg.fk_catalog_attribute_option_global_gender )) *100 > 60) 
AND (c.gender='male'), 'Men',
IF (((sum( lgg.fk_catalog_attribute_option_global_gender = 2 ) / count( lgg.fk_catalog_attribute_option_global_gender )) *100 > 60) 
AND (c.gender='female'), 'Women', '') ) as calculatedGender,
'ID'
FROM catalog_attribute_link_global_gender AS lgg
INNER JOIN catalog_simple AS cs ON cs.fk_catalog_config = lgg.fk_catalog_config
INNER JOIN sales_order_item AS soi ON soi.sku = cs.sku
INNER JOIN sales_order AS so ON soi.fk_sales_order = so.id_sales_order 
INNER JOIN customer as c ON c.id_customer = so.fk_customer
WHERE lgg.fk_catalog_attribute_option_global_gender IN (1,2)
AND so.created_at BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) AND NOW()
GROUP BY so.fk_customer
HAVING count( lgg.fk_catalog_attribute_option_global_gender ) > 2) B
ON A.'ID' = B.'ID'
SET A.fk_customer = B.fk_customer,
A.gender = B.calculatedGender;

只要您找出将在其上连接表 A 和 B 的“ID”列，这将为您提供所需的结果。