mysql - 插入引用另一个表的值

Question

我创建了以下简单的数据模型:

我使用了以下插入语句来插入值:

1) 表格产品:

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 中。
正如您在 datamodel 中看到的，Products_per_Order 表包含 Price 列。在此列中，我想插入引用列 Products_idProducts 中的 ID 的价格。因此，我尝试使用以下 insert 语句，但到目前为止无法正常工作:

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

你知道如何解决这个问题吗？

Answer 1

您可以使用子查询从 Products 表中提取它。

("2","1","Select Price from test.products where test.products.idProducts = '1')","10");