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

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