通过货币换算获取每种产品类型的最低价格 [英] Get the minimum price of each product type with currency conversion

问题描述

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

I would like to select the cheapest (including the cost of shipping, with prices converted to the local currency) of each of the product types. cheapest = (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        |
-----------------------------------------------

如果所有卖家都使用单一货币并且我不增加运费，我就能得到我想要的结果:

If all of the sellers used a single currency and I wasn't adding the cost of shipping, I could get the result I wanted with:

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

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

But I cannot figure out where to go from here. Any help would greatly appreciated.

推荐答案

    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

这篇关于通过货币换算获取每种产品类型的最低价格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！