代码之家 › 专栏 › 技术社区 › Michi

带计算列的Insert语句

mysql-workbench sql mysql

0

Michi · 技术社区 · 6 年前

我创建了以下简单的 DataModel

我使用了以下方法 insert statements 要插入值,请执行以下操作:

产品 :

INSERT INTO test.products
(ProductName, Price)
VALUES 
("Product A","99,99"),
("Product B","49,95"), 
("Product C","5,95");

2) 桌子命令

INSERT INTO test.orders
(Customer)
VALUES 
("Customer A"),
("Customer B"), 
("Customer B");

现在,在桌子上 Products_per_Order OrderValue 乘以 Price 和 Quantity . 因此,在回答问题时,我尝试了以下两种选择 here 但还不能让它们发挥作用:

专栏 价格 是根据价格来计算的吗 idProduct 从桌子上 Products .

备选案文1:

INSERT INTO test.products_per_order
(Orders_idOrders, Products_idProducts, Price, Quantity, OrderValue)
VALUES
("1","1",(Select Price from test.products where test.products.idProducts = "1"),"5",(Price * Quantity)),
("1","2",(Select Price from test.products where test.products.idProducts = "2"),"4",(Price * Quantity)),
("2","1",(Select Price from test.products where test.products.idProducts = "1"),"10",(Price * Quantity)),
("3","2",(Select Price from test.products where test.products.idProducts = "2"),"3",(Price * Quantity)),
("3","3",(Select Price from test.products where test.products.idProducts = "3"),"9",(Price * Quantity));

备选案文2:

INSERT INTO test.products_per_order 
(Orders_idOrders, Products_idProducts, Price, Quantity, OrderValue)
    SELECT m.idOrder, m.idProduct, p.price, m.qty, p.price * m.qty
    FROM (SELECT 1 as idOrder, 1 as idProduct, 5 as qty UNION ALL
          SELECT 1 as idOrder, 2 as idProduct, 4 as qty UNION ALL
          SELECT 2 as idOrder, 1 as idProduct, 10 as qty UNION ALL
          SELECT 3 as idOrder, 2 as idProduct, 3 as qty UNION ALL
          SELECT 3 as idOrder, 3 as idProduct, 9 as qty
         ) m
    LEFT JOIN test.products p on p.idProducts = m.idProduct;

2 回复 | 直到 6 年前

1

Zohar Peled 6 年前

是你写的 OrderValue

使用 alter table 改变现状列到生成的列,如下所示:

alter table test.products_per_order 
modify column OrderValue int generated always as (Price * Quantity) stored;

通过这种方式,您根本不需要插入它-您的插入变成如下所示:

INSERT INTO test.products_per_order 
(Orders_idOrders, Products_idProducts, Price, Quantity)
    SELECT m.idOrder, m.idProduct, p.price, m.qty
    FROM (SELECT 1 as idOrder, 1 as idProduct, 5 as qty UNION ALL
          SELECT 1 as idOrder, 2 as idProduct, 4 as qty UNION ALL
          SELECT 2 as idOrder, 1 as idProduct, 10 as qty UNION ALL
          SELECT 3 as idOrder, 2 as idProduct, 3 as qty UNION ALL
          SELECT 3 as idOrder, 3 as idProduct, 9 as qty
         ) m
    LEFT JOIN test.products p on p.idProducts = m.idProduct;

2

0

Michi 6 年前

prices Products 需要使用 . 作为十进制分隔符,而不是 ,

如果修改 INSERT STATEMENT 产品 致:

INSERT INTO test.products
(ProductName, Price)
VALUES 
("Product A","99.99"),
("Product B","49.95"), 
("Product C","5.95");

然后在中运行代码 选择2

INSERT INTO test.products_per_order 
(Orders_idOrders, Products_idProducts, Price, Quantity, OrderValue)
    SELECT m.idOrder, m.idProduct, p.price, m.qty, p.price * m.qty
    FROM (SELECT 1 as idOrder, 1 as idProduct, 5 as qty UNION ALL
          SELECT 1 as idOrder, 2 as idProduct, 4 as qty UNION ALL
          SELECT 2 as idOrder, 1 as idProduct, 10 as qty UNION ALL
          SELECT 3 as idOrder, 2 as idProduct, 3 as qty UNION ALL
          SELECT 3 as idOrder, 3 as idProduct, 9 as qty
         ) m
    LEFT JOIN test.products p on p.idProducts = m.idProduct;