php - 将数据从一个表插入到另一个表 - MySQL

Question

所以我想开一家小型在线商店。我有 2 个表:cart_products 和 order_products。在这些表中，可以将两种类型的项目添加到购物车中:promotion 和 products。这两种类型的项目存储在不同的表中，称为:promotions 和 products。

最初所有产品/促销都被添加到购物车表中，一旦用户结帐，它们就会被转移到订单表中并从购物车中删除表。

如果所选项目是产品，则 promotion_id 值默认设置为 0。如果选择的商品是促销商品，则反之亦然。这是我的基本结构:

cart_products

----------------------------------------------------------------------------------
| cart_products_id |    cart_id    |   product_id | promotion_id      | quantity |
----------------------------------------------------------------------------------
| 6                |       1       |   5          | 0                 | 2  
---------------------------------------------------------------------------------

order_products

---

----------------------------------------------------------------------------------
| order_id |   user_id    | product_id | promotion_id      | price    | quantity |
----------------------------------------------------------------------------------

我遇到的问题是尝试LEFT JOIN 产品/促销以获取所选项目的价格。到目前为止，这是我的查询。

INSERT INTO order_details (order_id, user_id, product_id, promotion_id, price, quantity)
VALUES(
  '6', 
  '7',
  (SELECT 
    cart_products.product_id, 
    cart_products.promotion_id,
    IF(cart_products.promotion_id = '0', products.price, promotions.price),
    cart_products.quantity

  FROM cart_products


  LEFT JOIN products
  ON cart_products.product_id = products.product_id

  LEFT JOIN promotions
  cart_products.promotion_id = promotions.promotion_id

  WHERE cart_products.cart_id = '6')
)

但是，这会给我一个错误 Not unique table/alias。有谁知道我该怎么做？非常感谢任何帮助!

Answer 1

您可以简单地选择常量，而不是像您那样定义值，这样您就可以使用 INSERT INTO SELECT 语法:

INSERT INTO order_details (order_id, user_id, product_id, promotion_id, price, quantity)
SELECT (
    '6', 
    '7',
    cart_products.product_id, 
    cart_products.promotion_id,
    IF(cart_products.promotion_id = '0', products.price, promotions.price),
    cart_products.quantity
  FROM cart_products
  LEFT JOIN products
    ON cart_products.product_id = products.product_id
  LEFT JOIN promotions
    ON cart_products.promotion_id = promotions.promotion_id
  WHERE cart_products.cart_id = '6'
)

此外，我相信您忘记了第二个左连接的“ON”子句