单个表中的多种货币

How to make a MySQL table that could contain multiple type of currencies ($, Euros, ...) ? And if I want to make a final report is their a way to make a sum of those currencies other than adding multiple if statements to the sum ?

如何制作一个可能包含多种货币（$，Euros，...）的MySQL表？如果我想做一个最终报告，他们可以通过在总和中添加多个if语句来计算这些货币的总和吗？

2 个解决方案

#1

There may be several aspects involved. So here is some brainstorming:

可能涉及到几个方面。所以这是一些头脑风暴：

• You offer the same product in different currencies, e.g. a toy for 10 EUR or 11 USD. So you'd probably have an item table plus an item_price table, where the latter has product number, currency and price. (An alternative would be to have just one price, e.g. in USD and a currency table with conversion rates and you'd calculate the foreign price. But then you'd get "ugly" prices, e.g. 10.57, rather than typical prices like 10.50, 10.90 or 10.99. But well, you could have a price adjustment funtion for that, too. And you'd have to keep your conversion tables up-to-date or live with possible losses.)
• 您提供不同货币的相同产品，例如玩具10欧元或11美元。所以你可能有一个项目表和一个item_price表，后者有产品编号，货币和价格。 （一种替代方案是只有一个价格，例如以美元和转换率的货币表，你计算国外价格。但是你会得到“丑陋”的价格，例如10.57，而不是像10.50这样的典型价格。 ，10.90或10.99。但是，您也可以为此设置价格调整功能。而且您必须保持转换表的最新状态或者可能存在损失。）
• A customer buys products and pays the bill. The bill is in one currency (store price and currency in the bill), but you also get your money in your currency, as PayPal or the bank convert it at a daily rate. Do you need to store this real amount, too? Then that would be another column in your bill.
• 客户购买产品并支付账单。该账单采用一种货币（商店价格和账单中的货币），但您也可以用您的货币获得货币，因为PayPal或银行以每日汇率兑换。你还需要存储这个真实金额吗？那么那将是你账单中的另一栏。
• I don't know how it is about taxes. Different currencies sounds like different countries. Maybe you'd have to deal with these in your database, too. I just don't know. The prices you show and store are usually gross prices (the price a customer actually pays) and the taxes (could be different VAT percentages with different products in one single bill) would have to be calculated.
• 我不知道税收是怎么回事。不同的货币听起来像不同国家。也许您还必须在数据库中处理这些问题。我只是不知道。您显示和存储的价格通常是总价格（客户实际支付的价格）和税收（可能是单一账单中不同产品的不同增值税百分比）必须计算。
• As to getting sums: with all the information stored you'd get them with joins from the tables. No if-then-else in my opinion.
• 至于获得总和：所有存储的信息都可以从表中获得连接。不，如果 - 那么 - 在我看来。

#2

• As per my opinion you can create a Country Table which contains CountryID, CountryName, CurrencyCode, ExchangeRate. Now In that country table you have to add all countries which you want to add but you have to keep one thing in mind that you have to decide 1 currency as base currency and put exchangeRate as 1 for that currency and convert all other currencies exchangeRate based on the base currency and insert into that table. i.e. Keep base currency as USD so insert 1 record with USD and exchangeRate must be 1 for that currency. Suppose I am adding India as country than exchangeRate for that country is based on USD as 66.40 Rs. insert all other countries according to this entries.

  根据我的意见，您可以创建一个包含CountryID，CountryName，CurrencyCode，ExchangeRate的Country Table。现在在该国家/地区表中，您必须添加要添加的所有国家/地区，但您必须记住一点，您必须将1种货币确定为基础货币，并将exchangeRate设置为该货币的1并转换所有其他货币exchangeRate在基础货币上并插入该表。即将基础货币保持为美元，因此在美元中插入1条记录，而对于该货币，exchangeRate必须为1。假设我将印度作为国家而不是对该国家的交换率进行加权，因为美元为66.40卢比。根据此条目插入所有其他国家/地区。

• Now when you want to add any transaction related to money in any table then keep exchangeRate column with that amount column. ind insert the exchangeRate and amount same as user's currency. i.e. If my user is in India country, so it's currency is INR. Now that user wants to add 1000 Rs. then you have to enter that 1000 Rs amount in transaction table as it is without any conversion with exchange Rate of 66.40 (fetch from country table). Same way If user currency is USD and he wants to add a transaction of 100$ than store 100 as Amount and exchangeRate as 1.

  现在，当您要在任何表中添加与money相关的任何事务时，请将exchangeRate列保留为该amount列。 ind插入exchangeRate，金额与用户的货币相同。即如果我的用户在印度国家，那么它的货币是INR。现在该用户想要添加1000卢比。那么你必须在交易表中输入1000卢比的金额，因为它没有任何转换，汇率为66.40（从国家表中获取）。同样的方法如果用户货币是美元并且他想要将100美元的交易作为金额添加，而将商店100的交易添加为1，则交换率为1。

• Now when you want to create any reports then just divide exchangeRate with Amount, so you will get report in your base currency and after conversion in base currency you can generate each report in any currency as per your requirement.
• 现在，当您想要创建任何报表时，只需将exchangeRate与金额分开，这样您就可以获得基础货币的报表，并且在转换为基础货币后，您可以根据您的要求以任何货币生成每个报表。