mysql - 通过货币换算获取每种产品类型的最低价格

Question

我想选择每种产品类型中最便宜的(包括运费，价格已转换为本地货币)。最便宜 = (product.price + product.shipping) * seller.to_aud

我的数据库有如下表:

PRODUCTS                                           SELLERS
-----------------------------------------------    --------------------------
| id | type_id | seller_id | price | shipping |    | id | currency | to_aud |
-----------------------------------------------    --------------------------
| 1  | 1       | 1         | 10    | 5        |    | 1  | usd      | 0.9875 |
-----------------------------------------------    --------------------------
| 2  | 1       | 2         | 10    | 2        |    | 2  | gbp      | 1.6000 |
-----------------------------------------------    --------------------------
| 3  | 1       | 1         | 13    | 0        |
-----------------------------------------------
| 4  | 2       | 1         | 8     | 4        |
-----------------------------------------------
| 5  | 2       | 2         | 8     | 2        |
-----------------------------------------------
| 6  | 2       | 2         | 15    | 0        |
-----------------------------------------------

如果所有卖家都使用一种货币并且我没有添加运费，我可以获得我想要的结果:

SELECT a.id, a.price
FROM
(
 SELECT type_id, min(price) as minprice
 FROM products
 GROUP BY type_id
) AS b INNER JOIN products as a on a.type_id = b.type_id and a.price = b.minprice
ORDER BY price

但我不知道从这里去哪里。任何帮助将不胜感激。

Answer 1

    SELECT a.id, a.price*ISNULL(s.to_aud,1) as minprice
FROM
(
 SELECT type_id, min((price+shipping)) as minprice
 FROM products
 GROUP BY type_id
) AS b INNER JOIN products as a on a.type_id = b.type_id and (a.price+a.shipping) = b.minprice
Inner join sellers s on s.id = a.seller_id
ORDER BY price