mysql - 如何根据 3 个表对特定数据进行 SUM()

Question

产品具有不同的选项(属性)，具体取决于您获得产品组合的可能选项。我想编辑我的产品的每种组合的价格。对于每个组合，价格应等于该组合所有属性的总和。注意:根据产品的不同，同一属性可能会对价格产生两种不同的影响。

我有如下 3 个表:

ps_product_attribute_shop - list of attributes with price impact depending of a combination (id_product_attribute).

+----------------------+--------+------------+
| id_product_attribute | price  | id_product |
+----------------------+--------+------------+
| 27934                | 50.000 | 9779       |
+----------------------+--------+------------+
| 27935                | 24.000 | 9780       |
+----------------------+--------+------------+

ps_attribute_impact - list of each attributes and the product they belong to and their price impact

+------------+--------------+--------+
| id_product | id_attribute | price  |
+------------+--------------+--------+
| 9778       | 45           | 10.000 |
+------------+--------------+--------+
| 9779       | 46           | 00.000 |
+------------+--------------+--------+
| 9779       | 47           | 00.000 |
+------------+--------------+--------+
| 9779       | 55           | 00.000 |
+------------+--------------+--------+
| 9779       | 56           | 46.000 |
+------------+--------------+--------+
| 9779       | 57           | 67.000 |
+------------+--------------+--------+

ps_product_attribute_combination - matching of attributes and product combinations (here the combination 27934 has two attributes => 46, 56)

+--------------+----------------------+
| id_attribute | id_product_attribute |
+--------------+----------------------+
| 46           | 27934                |
+--------------+----------------------+
| 56           | 27934                |
+--------------+----------------------+
| 46           | 27935                |
+--------------+----------------------+
| 57           | 27935                |
+--------------+----------------------+

我正在尝试更新 ps_product_attribute_shop 中每个产品组合的所有价格。

我尝试在 phMyAdmin 中使用以下查询:

UPDATE ps_product_attribute_shop
SET ps_product_attribute_shop.price = 
(
   SELECT SUM(ps_attribute_impact.price) 
   FROM ps_attribute_impact 
   WHERE ps_product_attribute_shop.id_product = ps_attribute_impact.id_product 
         AND ps_attribute_impact.id_attribute IN 
         (
            SELECT ps_product_attribute_combination.id_attribute 
            FROM ps_product_attribute_combination 
            WHERE ps_product_attribute_combination.id_product_attribute = 
                  ps_product_attribute_shop.id_product_attribute
         )
);

但是我在 phpMyAdmin 中遇到以下错误:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1

Answer 1

事实上，以下查询有效。

UPDATE ps_product_attribute_shop
SET ps_product_attribute_shop.price = 
(
   SELECT SUM(ps_attribute_impact.price) 
   FROM ps_attribute_impact 
   WHERE ps_product_attribute_shop.id_product = ps_attribute_impact.id_product 
         AND ps_attribute_impact.id_attribute IN 
         (
            SELECT ps_product_attribute_combination.id_attribute 
            FROM ps_product_attribute_combination 
            WHERE ps_product_attribute_combination.id_product_attribute = 
                  ps_product_attribute_shop.id_product_attribute
         )
);

问题是当我单击 phpMyAdmin 中的“模拟请求”按钮时。但是当我单击“执行”按钮时，一切正常。

感谢大家的帮助。