mysql - 计算列，还有什么比我的子查询更好的方法

我在尝试计算列时遇到麻烦

DF1

Date         transaction_number  tender_amt   tender_type
2017-01-01   001                 20           euros
2017-01-01   001                 10           american
2017-01-01   002                 20           american
2017-01-02   001                 40           american
2017-01-01   003                 40           euros
2017-01-01   004                 50           american

如何创建一个基于交易编号和日期将bid_amt与EUR / America分开的计算列？

DF所需

Date         transaction_number  tender_amt   tender_type     exchange_rate(euro/american)
2017-01-01   001                 20           euros            2
2017-01-01   001                 10           american         2
2017-01-01   002                 20           american         (null)
2017-01-02   001                 40           american         (null)
2017-01-01   003                 40           euros            (null)
2017-01-01   004                 50           american         (null)

我的尝试

Select Date, transaction_number, tender_amt, tender_type,
exchange_rate = (Select O.tender_amt / B.tender_amt as exchange_rate
From df1 O
Inner Join df1 as B on B.Date = O.Date and B.transaction_number = O.transaction_number
where  O.transaction_number = a.transaction_number
and O.Date = a.Date and O.tender_type = 'euro' and B.tender_type = 'american'
)
from df1 a

有没有更好的方法？

最佳答案

让我假设SQL Server。您可以使用窗口功能：

select df1.*,
       (max(case when tender_type = 'euros' then tender_amt end) over (partition by Date, transaction_number) /
        max(case when tender_type = 'american' then tender_amt end) over (partition by Date, transaction_number)
       ) as exchange_rate
from df1;

关于mysql - 计算列，还有什么比我的子查询更好的方法，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/49161479/