时间戳

首页 > 解决方案 > 如何根据聚合函数的结果更新属性

mysql - 如何根据聚合函数的结果更新属性

问题描述

我是 mySQL 的新手。我想根据零售价的值和订购的产品数量来更新表格的订单价格。我有以下表格（针对这个问题进行了简化）：

Products：

ProductID
Retail_price

Sales_orders：

SalesOrdersID
Order_price //This is the derived attribute that I want to update when the below Sales_products table is updated.

Sales_products：

SalesOrdersID
ProductID
Quantity

以下代码的工作原理是我得到了正确的Order_price结果，它在自己的表中输出。但我想更新属性Sales_orders.Order_price，而不是在表格中调出订单价格。

SELECT 
    sales_product.SalesOrdersID, 
    SUM(Quantity * Retail_price) as "Total price of order"
FROM 
    sales_product
    LEFT JOIN products ON sales_product.ProductID = products.ProductID
GROUP BY sales_product.SalesOrdersID

我也尝试过触发器，但是当我尝试在Sales_orders.

CREATE trigger find_order_price2
AFTER INSERT ON Sales_products
FOR EACH ROW

BEGIN
    DECLARE price int;
SELECT
    sales_product.SalesOrdersID, SUM(Quantity * Retail_price) INTO price
FROM
    sales_product
        LEFT JOIN
        products ON sales_product.ProductID = products.ProductID;
UPDATE sales_orders
set Order_price = price;
END;
$$
DELIMITER ;

返回错误：

错误代码：1222。使用的 SELECT 语句具有不同的列数

希望有人可以帮助解决这个问题？

标签： mysqlsqldatabase-trigger

解决方案

以下触发代码应该可以完成工作：

DELIMITER $$
CREATE trigger update_sales_order_price
AFTER INSERT ON Sales_products
FOR EACH ROW
BEGIN

    UPDATE Sales_orders so
    SET Order_price = (
        SELECT SUM(sp.Quantity * p.Retail_price)
        FROM Sales_products sp
        INNER JOIN Products p ON p.ProductID  = sp.ProductID 
        WHERE sp.SalesOrdersID = so.SalesOrdersID
    )
    WHERE SalesOrdersID = NEW.SalesOrdersID;

END;
$$
DELIMITER;

DB Fiddle 上的演示：

考虑以下布局：

产品：

| ProductID | retail_price |
| --------- | ------------ |
| 1         | 10           |
| 2         | 20           |

销售产品：

| SalesOrdersID | ProductID | Quantity |
| ------------- | --------- | -------- |
| 1             | 1         | 10       |

销售订单：

| SalesOrdersID | Order_price |
| ------------- | ----------- |
| 1             | 100         |

现在，有了触发器，我们在Sales_items表中插入一条与同一个 sales_order 相关的新记录：

insert into Sales_products values(1, 2, 10);

插入后，这里是Sales_orders表的（更新的）内容：

| SalesOrdersID | Order_price |
| ------------- | ----------- |
| 1             | 300         |

推荐阅读