时间戳

首页 > 解决方案 > 用 WHERE 子句划分 2 列

sql - 用 WHERE 子句划分 2 列

问题描述

要求：

将欧元汇率除以美元汇率并在新列中显示结果。例如，0.912/0.822 = 1.1。我想要一个名为“EUR/USD”的新列中的 1.1

我将不得不针对几种不同的汇率进行此划分，但我们可以使用 EUR/USD 作为示例。

询问

select er.Rate, wc.CurrencyCode + ' ' + wc.Descr as [Currency], er.EDate as [Exchange Date], ISNULL(wc.MonSymbol, ' ') AS [Symbol], er2.Rate as [Rate2]
from ExchangeRate er
JOIN WorldCurrency wc on er.ExCurrencyId = WC.InCurrencyId
JOIN ExchangeRate er2 on er.InExchangeRateId = er2.InExchangeRateId
inner join (
    select wc.CurrencyCode, max(er.EDate) as MaxDate
    from ExchangeRate er
    JOIN WorldCurrency wc on er.ExCurrencyId = WC.InCurrencyId
    group by wc.CurrencyCode
) erm on wc.CurrencyCode = erm.CurrencyCode and er.EDate = erm.MaxDate

当前结果

在此处输入图像描述

架构

试图

select b.*,max(case when b.currency='EUR Euro' then b.Rate end)/max(case when 

     b.currency='USD US Dollar' then b.Rate end) as [EUR/USD] from

    (select er.Rate as Rate, wc.CurrencyCode + ' ' + wc.Descr as [Currency], er.EDate 
    as [Exchange Date], ISNULL(wc.MonSymbol, ' ') AS [Symbol], er2.Rate as [Rate2]
    from ExchangeRate er
    JOIN WorldCurrency wc on er.ExCurrencyId = WC.InCurrencyId
      JOIN ExchangeRate er2 on er.InExchangeRateId = er2.InExchangeRateId
  inner join (
     select wc.CurrencyCode, max(er.EDate) as MaxDate
       from ExchangeRate er
       JOIN WorldCurrency wc on er.ExCurrencyId = WC.InCurrencyId
     group by wc.CurrencyCode
    ) erm on wc.CurrencyCode = erm.CurrencyCode and er.EDate = erm.MaxDate)b
    group by b.rate, b.Currency, b.[Exchange Date], b.Symbol, b.Rate2

结果：

在此处输入图像描述

解决方案：

select er.Rate, wc.CurrencyCode + ' ' + wc.Descr as [Currency], er.EDate as [Exchange Date], ISNULL(wc.MonSymbol, ' ') AS [Symbol],
[eur].Rate/[usd].rate as [EUR/USD], [usd].Rate/[zar].rate as [USD/ZAR], [eur].Rate/[zar].rate as [EUR/ZAR], [eur].Rate/[nok].rate as [EUR/NOK],
[eur].Rate/[bwp].rate as [EUR/BWP], [bwp].Rate/[zar].rate as [ZAR/BWP]
from ExchangeRate er
JOIN WorldCurrency wc on er.ExCurrencyId = WC.InCurrencyId
inner join (
    select wc.CurrencyCode, max(er.EDate) as MaxDate
    from ExchangeRate er
    JOIN WorldCurrency wc on er.ExCurrencyId = WC.InCurrencyId
    group by wc.CurrencyCode
) erm on wc.CurrencyCode = erm.CurrencyCode and er.EDate = erm.MaxDate
OUTER APPLY
(SELECT er3.Rate, wc3.CurrencyCode + ' ' + wc3.Descr as [Currency], er3.EDate as [Exchange Date]
FROM ExchangeRate er3
JOIN WorldCurrency wc3 on er3.ExCurrencyId = WC3.InCurrencyId
WHERE wc3.CurrencyCode = 'USD' AND er3.EDate = erm.MaxDate) as [USD]

OUTER APPLY
(SELECT er4.Rate, wc4.CurrencyCode + ' ' + wc4.Descr as [Currency], er4.EDate as [Exchange Date]
FROM ExchangeRate er4
JOIN WorldCurrency wc4 on er4.ExCurrencyId = WC4.InCurrencyId
WHERE wc4.CurrencyCode = 'EUR' AND er4.EDate = erm.MaxDate) as [EUR]

OUTER APPLY
(SELECT er5.Rate, wc5.CurrencyCode + ' ' + wc5.Descr as [Currency], er5.EDate as [Exchange Date]
FROM ExchangeRate er5
JOIN WorldCurrency wc5 on er5.ExCurrencyId = WC5.InCurrencyId
WHERE wc5.CurrencyCode = 'ZAR' AND er5.EDate = erm.MaxDate) as [ZAR]

OUTER APPLY
(SELECT er6.Rate, wc6.CurrencyCode + ' ' + wc6.Descr as [Currency], er6.EDate as [Exchange Date]
FROM ExchangeRate er6
JOIN WorldCurrency wc6 on er6.ExCurrencyId = WC6.InCurrencyId
WHERE wc6.CurrencyCode = 'NOK' AND er6.EDate = erm.MaxDate) as [NOK]

OUTER APPLY
(SELECT er7.Rate, wc7.CurrencyCode + ' ' + wc7.Descr as [Currency], er7.EDate as [Exchange Date]
FROM ExchangeRate er7
JOIN WorldCurrency wc7 on er7.ExCurrencyId = WC7.InCurrencyId
WHERE wc7.CurrencyCode = 'BWP' AND er7.EDate = erm.MaxDate

) as [BWP]

标签： sqlsql-server

解决方案

尝试这个 

    select b.*,case when b.currency='EUR Euro' then b.Rate end/case when 

     b.currency='USD US Dollar' then b.Rate end as [EUR/USD] from

    (select er.Rate as Rate, wc.CurrencyCode + ' ' + wc.Descr as [Currency], er.EDate 
    as [Exchange Date], ISNULL(wc.MonSymbol, ' ') AS [Symbol], er2.Rate as [Rate2]
    from ExchangeRate er
    JOIN WorldCurrency wc on er.ExCurrencyId = WC.InCurrencyId
      JOIN ExchangeRate er2 on er.InExchangeRateId = er2.InExchangeRateId
  inner join (
     select wc.CurrencyCode, max(er.EDate) as MaxDate
       from ExchangeRate er
       JOIN WorldCurrency wc on er.ExCurrencyId = WC.InCurrencyId
     group by wc.CurrencyCode
    ) erm on wc.CurrencyCode = erm.CurrencyCode and er.EDate = erm.MaxDate)b

推荐阅读