如何创建视图来计算货币？

Question

我有以下表格：

Products
  product, idcurrency, value
   Prod1     1          5000
   Prod2     2          3000
   Prod3     3          2000

Currrency
  idcurrency, currencyID
     1          EUR
     2          USD
     3          DKK

 ForexExchange

   idforexExchange   providername, idbaseCurrency, isActive
      1                   XE           Eur            1
      2                  provider2     DKK            1

ForexRates
 idForexRates  idforexExchange, toCurrency, exchangeRate
  1                1               AED        2.3
  2                1               EGY        1.3
  3                1               GBP        2.4 
  4                2               AED        7.3
  5                3               EGY        6.4
  6                4               GBP        3.4 

我想把所有的产品都换成美元作为现货。

与查找表currency -we相关的currency字段具有forexexchange表，因此如果我们有多个汇率提供者，我们将根据基础货币获得每个汇率的货币汇率

-the外汇汇率表保存每个交换的汇率

-we可以从isActive设置活动交换，因此系统中将使用此汇率表

Expected Results 
product  Value ValueUSD
prod1    5000    25000
...............

Answer 1

我们开始吧：

-- sample data, for somebody else start
CREATE TABLE #Product
    ( Name varchar(50), IdCurrency int, Value decimal(15,2) )
CREATE TABLE #Currency
    ( IdCurrency int, CurrencyID char(3) )
CREATE TABLE #ForexExchange
    ( IdForexExchange int, IdBaseCurrency char(3), IsActive bit)
CREATE TABLE #ForexRates
    ( IdForexRates int, IdForexExchange int,
      ToCurrency char(3), ExchangeRate decimal(15,2) )
GO

INSERT INTO #Product VALUES ('Prod1', 1, 5000)
INSERT INTO #Product VALUES ('Prod2', 2, 3000)
INSERT INTO #Product VALUES ('Prod3', 3, 2000)
INSERT INTO #Currency VALUES (1, 'EUR')
INSERT INTO #Currency VALUES (2, 'USD')
INSERT INTO #Currency VALUES (3, 'DKK')
INSERT INTO #ForexExchange VALUES (1, 'EUR', 1)
INSERT INTO #ForexExchange VALUES (2, 'DKK', 1)
INSERT INTO #ForexRates VALUES (1, 1, 'AED', 2.3)
INSERT INTO #ForexRates VALUES (2, 1, 'EGY', 1.3)
INSERT INTO #ForexRates VALUES (3, 1, 'GBP', 2.4)
INSERT INTO #ForexRates VALUES (4, 2, 'AED', 7.3)
INSERT INTO #ForexRates VALUES (5, 3, 'EGY', 6.4)
INSERT INTO #ForexRates VALUES (6, 4, 'GBP', 3.4)
GO

和你的view语句：

SELECT
    #Product.Name,
    #Currency.CurrencyID as FromCurrency,
    #ForexRates.ToCurrency,
    #ForexRates.ExchangeRate,
    #Product.Value as OriginalValue, 
    #Product.Value * #ForexRates.ExchangeRate as CalculatedValue
FROM
    #Product LEFT JOIN
    #Currency ON #Product.IdCurrency = #Currency.IdCurrency LEFT JOIN
    #ForexExchange ON
        IsActive = 1 AND
        #Currency.CurrencyID = #ForexExchange.IdBaseCurrency LEFT JOIN
    #ForexRates ON #ForexExchange.IdForexExchange = #ForexRates.IdForexExchange
--WHERE
--  #ForexRates.ToCurrency = 'USD'

我保留了WHERE条款的注释，因为你没有任何美元货币可以显示。您不应该将该筛选器包含在查询中，因为它可以重用来以任何货币显示产品值：