使用连接在 MySQL 中转换货币 [英] Converting currency in MySQL using a join

问题描述

我有两张桌子:

不断更新的currency表(基于美元):

 +----+----------+-----------+
 | id | currency | value_usd |
 +----+----------+-----------+
 |  1 | USD      | 1         |
 |  2 | AUD      | 1.077315  |
 |  3 | GBP      | 0.620868  |
 |  4 | EUR      | 0.775338  |
 +----+----------+-----------+

我有一个 order 表，其中添加了新订单:

And I have an order table where new orders are added:

+----+-------------+----------+
| id | sales_total | currency |
+----+-------------+----------+
|  1 | 100         | USD      |
|  2 | 50          | GBP      |
|  3 | 75          | EUR      |
|  4 | 60          | GBP      |
+----+-------------+----------+

我有一个货币输入，它决定了我需要输出总计的货币类型，即使所有订单都以各种货币存储.

I have an input of currency, which dictates the type of currency that I need to output the totals in, even though all orders are stored in various currencies.

例如，如果 $currency = 'EUR'; 根据 currency<中的汇率查询 order 表时，所有总数必须以欧元为单位/代码> 表.像这样:

For example, if $currency = 'EUR'; all totals must be in EUR when querying the order table based on the rates in the currency table. Like so:

+----+-------------+----------+-----------------+
| id | sales_total | currency | converted_total |
+----+-------------+----------+-----------------+
|  1 | 100         | USD      | 77.53           |
|  2 | 50          | GBP      | 62.44           |
|  3 | 75          | EUR      | 75.00           |
|  4 | 60          | GBP      | 74.92           |
+----+-------------+----------+-----------------+

我该怎么做?我想我需要某种 CASE 语句?

How can I do this? I imagine that I'd need some sort of a CASE statement?

推荐答案

应该这样做:

SELECT o.*, sales_total * (c2.value_usd / c1.value_usd) as converted_total,
       c2.currency as converted_currency
FROM `order` o
JOIN `currency` c1 ON o.currency = c1.currency
JOIN `currency` c2 ON c2.currency = 'EUR'

虽然没有示例数据库很难测试 - 计算可能会出错，但原理很清楚.

Hard to test without a sample DB though - the calculation might be off but the principle is clear.

这篇关于使用连接在 MySQL 中转换货币的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！