计算另一个表中另一列的列值 [英] Calculate column value from another column in another table

问题描述

我有一张表格，其中包含商店中每件商品的单价和其他详细信息.

CREATE TABLE Item (Item_id CHAR(4), Description CHAR(40), Sizes CHAR(40), Weight REAL, Unit_price REAL, PRIMARY KEY(Item_id));

另外一个包含每个订单中包含的项目的详细信息.

CREATE TABLE Order_contains_items (Item_id CHAR(4), Order_no INT, Quantity_ordered INT, Ordered_price REAL, PRIMARY KEY(Item_id, Order_no), FOREIGN KEY(Order_no) REFERENCES Order(Order_no), FOREIGN KEY(Item_id) REFERENCES Item(Item_id));

现在我要计算

Order_contains_items.Ordered_price = Item.Unit_price * Order_contains_items.Quantity_ordered WHERE Order_contains_items.Item_id = Item.Item_id

请注意，我希望它成为表本身的一部分，而不是作为其他视图或查询.我怎样才能做到这一点?我为此研究了触发器和其他机制，但是它们是否适用于不同表中的值，尤其是在存在此类约束的情况下?

我根据从另一列计算出的列尝试了以下触发器?:

CREATE TRIGGER Order_price BEFORE INSERT ON Order_contains_items
    FOR EACH ROW BEGIN
        SET NEW.Ordered_price = Item.Unit_price * NEW.Quantity_ordered
            WHERE NEW.Item_id = Item.Item_i
END;

但是它似乎没有作用

解决方案

这是在MySQL中执行此操作的方法(您的问题同时被mysql和sql-server标记)

 CREATE TRIGGER tg_bi_order_contains_items 
BEFORE INSERT ON Order_contains_items
FOR EACH ROW
  SET NEW.Ordered_price = 
  (
    SELECT Unit_price * NEW.Quantity_ordered
      FROM Item
     WHERE Item_id = NEW.Item_id
     LIMIT 1
  );

CREATE TRIGGER tg_bu_order_contains_items 
BEFORE UPDATE ON Order_contains_items
FOR EACH ROW
  SET NEW.Ordered_price = 
  (
    SELECT Unit_price * NEW.Quantity_ordered
      FROM Item
     WHERE Item_id = NEW.Item_id
     LIMIT 1
  );
 

这里是 SQLFiddle 演示

I have a table that contains the unit price and other details of each item in a store.

CREATE TABLE Item (Item_id CHAR(4), Description CHAR(40), Sizes CHAR(40), Weight REAL, Unit_price REAL, PRIMARY KEY(Item_id));

And another one that contains details of the items contained in each order.

CREATE TABLE Order_contains_items (Item_id CHAR(4), Order_no INT, Quantity_ordered INT, Ordered_price REAL, PRIMARY KEY(Item_id, Order_no), FOREIGN KEY(Order_no) REFERENCES Order(Order_no), FOREIGN KEY(Item_id) REFERENCES Item(Item_id));

Now I want to compute

Order_contains_items.Ordered_price = Item.Unit_price * Order_contains_items.Quantity_ordered WHERE Order_contains_items.Item_id = Item.Item_id

Note that I want it to be a part of the table itself and not as a different view or a query. How can I do this? I looked into triggers and other mechanisms for this but do they work for values in different tables especially with such constraints?

I tried the following trigger based on Column calculated from another column? :

CREATE TRIGGER Order_price BEFORE INSERT ON Order_contains_items
    FOR EACH ROW BEGIN
        SET NEW.Ordered_price = Item.Unit_price * NEW.Quantity_ordered
            WHERE NEW.Item_id = Item.Item_i
END;

But it didn't seem to work

解决方案

Here is how you can do that in MySQL (your question is tagged with both mysql and sql-server)

CREATE TRIGGER tg_bi_order_contains_items 
BEFORE INSERT ON Order_contains_items
FOR EACH ROW
  SET NEW.Ordered_price = 
  (
    SELECT Unit_price * NEW.Quantity_ordered
      FROM Item
     WHERE Item_id = NEW.Item_id
     LIMIT 1
  );

CREATE TRIGGER tg_bu_order_contains_items 
BEFORE UPDATE ON Order_contains_items
FOR EACH ROW
  SET NEW.Ordered_price = 
  (
    SELECT Unit_price * NEW.Quantity_ordered
      FROM Item
     WHERE Item_id = NEW.Item_id
     LIMIT 1
  );

Here is SQLFiddle demo

这篇关于计算另一个表中另一列的列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！