因此,在这里,我有一个带有fromcurr_id和tocurr_id的exchange_rate表,根据ID的货币名称在另一个表currency中。
exchng_rate_id | fromcurr_id | tocurr_id | exchange_rate
----------------+-------------+-----------+---------------
1 | 1 | 2 | 5.0000000000
2 | 1 | 3 | 3.0000000000
3 | 1 | 4 | 6.0000000000
4 | 1 | 5 | 2.0000000000
5 | 2 | 3 | 5.0000000000
6 | 2 | 4 | 7.0000000000
7 | 2 | 5 | 3.0000000000
8 | 3 | 4 | 1.0000000000
9 | 3 | 5 | 4.0000000000
10 | 4 | 5 | 2.0000000000
这是货币表的说明:
Column | Type | Collation | Nullable | Default
---------+------------------------+-----------+----------+--------------------------------------
id | integer | | not null | nextval('currency_id_seq'::regclass)
curr_id | integer | | not null |
name | character varying(255) | | |
我需要显示哪个ID属于哪个货币名称。(即fromcurr_id =名称和tocurr_id =名称)
这就是我尝试过的...
Query1:在表上尝试了内部联接,但是结果仅产生fromcurr_id的名称。
select name, fromcurr_id, tocurr_id, name, exchange_rate
from exchange_rate
inner join currency on exchange_rate.fromcurr_id = currency.curr_id;
结果:
name | fromcurr_id | tocurr_id | name | exchange_rate
----------------+-------------+-----------+----------------+---------------
************** | 1 | 2 | ************** | 5.0000000000
************** | 1 | 3 | ************** | 3.0000000000
************** | 1 | 4 | ************** | 6.0000000000
************** | 1 | 5 | ************** | 2.0000000000
************* | 2 | 3 | ************ | 5.0000000000
************ | 2 | 4 |************** | 7.0000000000
************* | 2 | 5 | ************ | 3.0000000000
************** | 3 | 4 | ************** | 1.0000000000
************** | 3 | 5 | ************** | 4.0000000000
************** | 4 | 5 | ************** | 2.0000000000
(10 rows)
第一种情况下的名称与第二种情况下的名称相同,但与币种表不同。(必须检查数据的机密性)
Query2:尝试使用两个条件进行内部联接。
select name, fromcurr_id, tocurr_id, name, exchange_rate
from exchange_rate
inner join currency on exchange_rate.fromcurr_id = currency.curr_id
and exchange_rate.tocurr_id = currency.curr_id;
这没有结果。
您需要两个join
s,每种货币一个:
select er.*, cfrom.*, cto.*
from exchange_rate er inner join
currency cfrom
on er.fromcurr_id = cfrom.curr_id join
current cto
on er.tocurr_id = eto.curr_id
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句