MySQL - 从选择内部使用条件

Question

我有 3 个表:

• 订单:
  • 订单编号
  • 购买日期
  • ...
• 订单产品:
  • orders_products_id
  • 订单编号
  • 产品编号
  • 产品名称
  • 产品_数量
  • ...
• 产品_仓库:
  • 产品编号
  • 仓库编号
  • product_quantity_in_warehouse

现在我想要一个包含以下信息的表格:

• 当前日期
• 产品编号
• 产品名称
• 仓库编号
• current_quantity_in_warehouse
• 昨天卖出

到目前为止，我得到了以下内容:

SELECT DISTINCT  CURRENT_DATE AS `date`
                 orpr.products_id AS products_id
                 orpr.products_name AS products_name
                 pwq.warehouses_id AS warehouses_id
                 pwq.products_quantity AS current_quantity_in_warehouse
                 (  SELECT SUM(orpr.products_quantity)
                    FROM `products_warehouses` wpq
                    INNER JOIN `orders_products` orpr ON pwq.products_id = orpr.products_id
                    INNER JOIN `orders` or ON orpr.orders_id = or.orders_id
                    WHERE pwq.products_id = ??
                    AND   pwq.warehouses_id = ?? ) AS sold_yeserday
FROM             `orders_products` orpr
INNER JOIN       `orders` or ON orpr.orders_id = or.orders_id
INNER JOIN       `products_warehouses pwq ON pwq.products_id = orpr.products_id;

我要的是结果中每一行的??的products_id和warehouses_id的位置。

如果有人得到了一个更简单的例子，说明如何在 select 中使用 select(内部 select 从中使用 select 中的 where-condition id，当然这会改变每一行)，这也很好，只要因为我可以重现示例来解决我的问题。

提前致谢

您好，凯文·C

Answer 1

您应该提取内部 SELECT 语句并将结果放入表中，然后您可以在外部选择中与该表联接

SELECT SUM(orpr.products_quantity) As 'Sold_yesterday', pwd.products_id, pwd.warehouses_id
INTO #tempTable
FROM `products_warehouses` wpq
INNER JOIN `orders_products` orpr ON pwq.products_id = orpr.products_id
INNER JOIN `orders` or ON orpr.orders_id = or.orders_id


SELECT DISTINCT  CURRENT_DATE AS `date`
orpr.products_id AS products_id
orpr.products_name AS products_name
pwq.warehouses_id AS warehouses_id
pwq.products_quantity AS current_quantity_in_warehouse                
FROM             `orders_products` orpr
INNER JOIN       `orders` or ON orpr.orders_id = or.orders_id
INNER JOIN       `products_warehouses pwq ON pwq.products_id = orpr.products_id;
INNER JOIN       '#tempTable' tt On tt.products_id = pwd.products_id AND tt.warehouses_id = pwd.warehouses_id