嗨,我有一个表,其中有4列,我需要这是'sell_currency','sell_currency','buy_currency'和'buy_currency'.currency列是varchar,而amount是数字,我想要实现的是通过其货币计算金额的总和,所以这是我用来得到结果的SQL:
select
sell_currency,
sum(sell_amount) as sell_amount
null as buy_currency
null as buy_amount
from table1
group by
sell_currency
union
select
null as sell_currency,
null as sell_amount,
buy_currency,
sum(buy_amount) as buy_amount
from table1
group by
buy_currency
使用表1中的数据:
SELL_CURRENCY SELL_AMOUNT BUY_CURRENCY BUY_AMOUNT
A 123 D 1
B 234 E 2
B 345 D 3
A 222 (null) 0
C 4444 (null) 0
这是我从表中得到的结果:
SELL_CURRENCY SELL_AMOUNT BUY_CURRENCY BUY_AMOUNT
A 345 (null) (null)
B 579 (null) (null)
C 4444 (null) (null)
(null) (null) D 4
(null) (null) E 2
但是,我想删除这些null,并能够将行组合在一起,以实现如下所示的结果:
SELL_CURRENCY SELL_AMOUNT BUY_CURRENCY BUY_AMOUNT
A 345 D 4
B 579 E 2
C 4444 (null) (null)
我需要做什么才能达到这个结果?
为了了解更多信息,这4列也经常更新,因此在卖出和买入中可能有3或2种以上的独特货币.