MySQL 中的子查询和为多个外键选择最旧的行

Question

我有两个表：

product (idproduct, name, description, tax)

product_storage (idstorage, idproduct, added, quantity, price)

对于每种产品，它可能有不同的价格，并且最旧的产品是“首先出售”的产品，

例如在我拥有的存储中：

1, 1, 2010-01-01,  0, 10.0
2, 1, 2010-01-02,  0, 11.0
3, 1, 2010-01-03, 10, 12.0
4, 2, 2010-01-04,  0, 12.0
5, 2, 2010-01-05, 10, 11.0
6, 2, 2010-01-06, 10, 13.0
7, 3, 2010-01-07, 10, 14.0
8, 3, 2010-01-08, 10, 16.0
9, 3, 2010-01-09, 10, 13.0

现在我需要选择具有当前价格的所有产品，该产品位于product_storage中最旧的行中其中数量>每个产品为 0：

SELECT p.idproduct, p.name, p.tax,
       (SELECT s.price
        FROM product_storage s
        WHERE s.idproduct=p.idproduct AND s.quantity > 0
        ORDER BY s.added ASC
        LIMIT 1) AS price
FROM product p;

工作正常，但当我想在查询中计算含税价格时却行不通：

SELECT p.idproduct, p.name, p.tax,
       (SELECT s.price
        FROM product_storage s
        WHERE s.idproduct=p.idproduct AND s.quantity > 0
        ORDER BY s.added ASC
        LIMIT 1) AS price,
        (price * (1 + tax/100)) AS price_with_tax
FROM product p;

MySQL 说：

Unknown column 'price' in 'field list'

更新

使用子查询作为表几乎可以解决问题（查看答案） -现在唯一的问题是如何从product_storage中为多个外键选择最旧的行（每个idproduct只有一个）。

更新 2

感谢 cmptrgeekken 提供了很好的解决方案:))

Answer 1

使用子查询作为表可能会更好：

SELECT p.idproduct, p.name, p.tax, s.price, (s.price * (1 + p.tax/100)) as price_with_tax
FROM product p
INNER JOIN (SELECT idproduct, price
        FROM product_storage
        WHERE quantity > 0) s
ON p.idproduct = s.idproduct
INNER JOIN (SELECT idproduct, MIN(added) min
        FROM product_storage
        GROUP BY idproduct) f
ON s.idproduct = f.idproduct AND s.added = f.min

编辑：更新，因为税位于另一个表中，所以我必须移动该计算的位置。

编辑2：好的，再次改变一些事情以尝试正确过滤product_storage表。

Answer 2

您收到价格不存在错误的原因是您无法引用列列表中的别名列。要解决此问题，请将 price 值存储在用户定义的变量中并引用该变量，如下所示：

SELECT p.idproduct, p.name, p.tax,
       @price := (SELECT s.price
        FROM product_storage s
        WHERE s.idproduct=p.idproduct AND s.quantity > 0
        ORDER BY s.added ASC
        LIMIT 1) AS price,
        (@price * (1 + tax/100)) AS price_with_tax
FROM product p;

Answer 3

这几乎可以正常工作：

SELECT p.idproduct, p.name, p.tax,
       sub.price, (sub.price * (1+tax/100)) as price_with_tax
FROM product p,
    (SELECT s.idproduct, s.price
     FROM product_storage s
     WHERE quantity > 0
     ORDER BY added ASC) sub
WHERE p.idproduct=sub.idproduct

但是每个产品都会返回product_storage中的所有行：/

Answer 4

就 MySQL 而言，最后一次出现的单词“price”指的是 Product p 中的一个字段，因此会出现错误。您需要再次引用上面的别名子查询：

SELECT p.idproduct, p.name, p.tax,
       (SELECT s.price
        FROM product_storage s
        WHERE s.idproduct=p.idproduct AND s.quantity > 0
        ORDER BY s.added ASC
        LIMIT 1) AS price,
        (       (SELECT s.price
        FROM product_storage s
        WHERE s.idproduct=p.idproduct AND s.quantity > 0
        ORDER BY s.added ASC
        LIMIT 1) * (1 + tax/100)) AS price_with_tax
FROM product p;

Answer 5

首先，您想要获取产品的最低存储 ID。

SELECT idproduct, MIN(idstorage) idstorage FROM product_storage 
WHERE quantity>0 AND idproduct IN (#, #, ...)
GROUP BY idstorage

idstorage、idproduct、添加、数量、价格
然后，您可以使用最小存储 ID 加入该查询和 Product_storage 表（再次），以获取包含正确定价信息的产品信息。

SELECT idproduct, name, description, tax, ps.price, ps.quantity,
FROM product AS p
JOIN 
(SELECT idproduct, MIN(idstorage) idstorage FROM product_storage 
WHERE quantity>0 AND idproduct IN (#, #, ...)
GROUP BY idstorage) AS min_id
ON p.idproduct=min_id.idproduct
RIGHT JOIN product_storage AS ps ON ps.idstorage=min_id.idstorage
WHERE idproduct IN (#, #, ...)