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

8fsztsew  于 2021-06-26  发布在  Hive
关注(0)|答案(2)|浏览(335)

我有两个表:一个有汇率(英镑),另一个有不同货币的不同金额列。我将这两个表连接起来,并以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)
zqry0prt

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;
rjzwgtxy

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;

相关问题