sql - 单一查询中的多种货币转换

Question

请找到以下示例数据。有 2 个表交易和汇率。如果我需要将交易表转换为美元、英镑和欧元，那么下面是我尝试过的查询，但它没有给出所需的输出。

交易

货币	Local_Price
美元	1000
英镑	100
欧元	10
美元	100

汇率

From_Currency	To_Currency	汇率
美元	英镑	0.9
欧元	美元	1.1
英镑	美元	1.3
欧元	英镑	0.9

预期产出

货币	Local_Price	In_USD	In_GBP	In_Eur
美元	1000	1000	900	80
英镑	100	130	100	99
欧元	10	11	9	10
美元	100	100	90	98

询问

Select Currency,Local_price, local_price(coalesce(Exchange_Rate,1))  as In_USD,
from Transaction T
left join ExchangeRate ER on T.Currency=ER.From_Currency 
                         and To_Currency in ('USD','GBP','EUR')

我的汇率表中没有从美元到美元或欧元到欧元或英镑到英镑的数据。

请帮助我查询所需的输出。

标签： sqlsql-server

Answer 1

我已经在下面输入了您问题的答案。请务必在ExchangeRate表格中输入所有货币换算。如果需要，可以获取。

我还输入了使用的表格和输出结果。

select Currency,Local_Price,
CASE
    WHEN Currency = 'USD' THEN Local_Price
    WHEN Currency = 'GBP' THEN (select Exchange_Rate FROM ExchangeRate WHERE From_Currency = 'GBP' AND To_Currency = 'USD') * Local_Price 
    WHEN Currency = 'EUR' THEN (select Exchange_Rate FROM ExchangeRate WHERE From_Currency = 'EUR' AND To_Currency = 'USD') * Local_Price 
END AS In_USD,
CASE
    WHEN Currency = 'USD' THEN (select Exchange_Rate FROM ExchangeRate WHERE From_Currency = 'USD' AND To_Currency = 'GBP') * Local_Price
    WHEN Currency = 'GBP' THEN Local_Price 
    WHEN Currency = 'EUR' THEN (select Exchange_Rate FROM ExchangeRate WHERE From_Currency = 'EUR' AND To_Currency = 'GBP') * Local_Price 
END AS In_GBP,
CASE
    WHEN Currency = 'USD' THEN (select Exchange_Rate FROM ExchangeRate WHERE From_Currency = 'USD' AND To_Currency = 'EUR') * Local_Price
    WHEN Currency = 'GBP' THEN (select Exchange_Rate FROM ExchangeRate WHERE From_Currency = 'GBP' AND To_Currency = 'EUR') * Local_Price 
    WHEN Currency = 'EUR' THEN Local_Price 
END AS In_Eur

from [Transaction]

汇率表

交易表

结果表