sql—如何在配置单元中连接具有多个列的单个表？

8fsztsew 于 2021-06-26 发布在 Hive

关注(0)|答案(2)|浏览(334)

我有两个表：一个有汇率（英镑），另一个有不同货币的不同金额列。我将这两个表连接起来，并以gbp显示所有amount值，但对于许多表，我得到null。

> select * from xrates;
+--------------+--------------+--+
| xrates.curr  | xrates.rate  |
+--------------+--------------+--+
| GBP          | 1            |
| INR          | 89.74        |
| EUR          | 1.23         |
+--------------+--------------+--+

> select * from balances;
+-----------------------+-----------------+--------------------+-----------------+---------------------+-----------------+--+
| balances.acctbalance  | balances.acurr  | balances.interest  | balances.icurr  | balances.dividends  | balances.dcurr  |
+-----------------------+-----------------+--------------------+-----------------+---------------------+-----------------+--+
| 334.23                | GBP             | 1.2                | GBP             | 0                   | GBP             |
| 10000                 | INR             | 100                | EUR             | NULL                | GBP             |
+-----------------------+-----------------+--------------------+-----------------+---------------------+-----------------+--+

下面是连接这两个表的查询

SELECT 
  acctbalance, acurr, acctbalance/rate as `AB to GBP`,
  interest, icurr, interest/rate as `Ints to GBP`,
  dividends, dcurr, dividends/rate as `Divnd to GBP`
FROM    
   balances           --table 1
LEFT JOIN 
   xrates             --table 2
  on acurr = curr     --account balance currency
 and icurr = curr     --interest currency
 and dcurr = curr     --dividend currency

+--------------+--------+------------+-----------+--------+--------------+------------+--------+---------------+--+
| acctbalance  | acurr  | ab to gbp  | interest  | icurr  | ints to gbp  | dividends  | dcurr  | divnd to gbp  |
+--------------+--------+------------+-----------+--------+--------------+------------+--------+---------------+--+
| 334.23       | GBP    | 334.23     | 1.2       | GBP    | 1.2          | 0          | GBP    | 0             |
| 10000        | INR    | NULL       | 100       | EUR    | NULL         | NULL       | GBP    | NULL          |
+--------------+--------+------------+-----------+--------+--------------+------------+--------+---------------+--+
(2nd row - converted to GBP columns - has nulls)

sql Hive hiveql

来源：https://stackoverflow.com/questions/51863876/how-to-join-single-table-with-multiple-columns-in-hive

2条答案

按热度按时间

zqry0prt1#

由于这三种不同数额的货币都可能不同，因此不能只加入一次汇率。只有在货币相同的情况下，才能找到汇率记录。所以你得到 NULL 因为没有找到汇率，所以货币不同。试着分别加入三次。

SELECT b.acctbalance,
       b.acurr,
       b.acctbalance / xa.rate `AB to GBP`,
       b.interest,
       b.icurr,
       b.interest / xi.rate `Ints to GBP`,
       b.dividends,
       b.dcurr,
       b.dividends / xd.rate `Divnd to GBP`
       FROM balances b
            LEFT JOIN xrates xa
                      ON xa.curr = b.acurr
            LEFT JOIN xrates xi
                      ON xi.curr = b.icurr
            LEFT JOIN xrates xd
                      ON xd.curr = b.dcurr;

赞(0）回复(0）举报 2021-06-26

rjzwgtxy2#

你需要两个 JOIN 再一次：

SELECT b.acctbalance, b.acurr, b.acctbalance / r1.rate as `AB to GBP`,
       b.interest, b.icurr, b.interest / r2.rate as `Ints to GBP`,
       b.dividends, b.dcurr, b.dividends / r3.rate as `Divnd to GBP`
FROM balances b LEFT JOIN 
     xrates r1           
     ON b.acurr = r1.curr LEFT JOIN
     xrates r2  
     ON b.icurr = r2.curr LEFT JOIN
     xrates r3 
     ON b.dcurr = r3.curr;

赞(0）回复(0）举报 2021-06-26

我来回答

sql—如何在配置单元中连接具有多个列的单个表？

2条答案

相关问题

热门标签

最新问答