mysql - 将 id 替换为现有查询中另一个表的名称

Question

这是我的查询，用于获取每个 Product_Id 的剩余库存。但是，我不确定如何从 Product 表中添加 Product_Name 。我只想将 i.Product_Id 替换为 p.Product_Name，其中 p 是 Product 的别名。我知道 Product p 应该插入 FROM 中，但不确定如何将其与给定的查询连接起来。

顺便说一下，Product.Id = Inventory.Product_Id

SELECT 
i.Product_Id,
i.Stocks,
s.Sales,
i.Stocks - s.Sales AS Remaining
FROM (SELECT product_id, COALESCE(SUM(quantity),0) AS Stocks FROM inventory GROUP BY product_id) i
LEFT JOIN (SELECT product_id, COALESCE(SUM(quantity),0) AS Sales FROM sales_detail GROUP BY product_id ) s
USING(product_id);

提前谢谢大家!

更新!!下面的查询是我更新的查询，有人可以检查我是否将产品与现有的 JOIN USING 正确链接，谢谢!

SELECT 
i.Product_Id,
p.Product_Name,
i.Stocks,
s.Sales,
i.Stocks - s.Sales AS Remaining
FROM (SELECT product_id, COALESCE(SUM(quantity),0) AS Stocks FROM inventory GROUP BY product_id) i
LEFT JOIN (SELECT product_id, COALESCE(SUM(quantity),0) AS Sales FROM sales_detail GROUP BY product_id ) s
USING(product_id)
JOIN Product p
ON i.Product_Id=p.Id;

Answer 1

您只需JOIN 到Products 表即可。

SELECT 
    p.Product_Name,
    i.Stocks,
    s.Sales,
    i.Stocks - s.Sales AS Remaining
FROM (SELECT product_id, COALESCE(SUM(quantity),0) AS Stocks FROM inventory GROUP BY product_id) i
JOIN Products AS p USING (product_id)
LEFT JOIN (SELECT product_id, COALESCE(SUM(quantity),0) AS Sales FROM sales_detail GROUP BY product_id ) s
USING(product_id)