MySQL在匹配特定值时加入条件

Question

我有一个 MySQL 数据库，其中包含表 countries 和 exchange_rates:

mysql> SELECT * FROM countries;
+----------------+----------+----------------+
| name           | currency | GDP            |
+----------------+----------+----------------+
| Switzerland    | CHF      |   163000000000 |
| European Union | EUR      | 13900000000000 | 
| Singapore      | SGD      |   403000000000 |
| USA            | USD      | 17400000000000 |
+----------------+----------+----------------+
mysql> SELECT * FROM exchange_rates;
+----------+------+
| currency | rate |
+----------+------+
| EUR      |  0.9 |
| SGD      | 1.37 |
+----------+------+

我想要一个连接表，其中包含以美元显示 GDP 的附加列。

目前我有这个:

mysql> SELECT countries.name, GDP, countries.GDP/exchange_rates.rate AS 'GDP US$'
    -> FROM countries, exchange_rates
    -> WHERE exchange_rates.currency=countries.currency;
+----------------+----------------+----------------+
| name           | GDP            | GDP US$        |
+----------------+----------------+----------------+
| European Union | 13900000000000 | 15444444853582 |
| Singapore      |   403000000000 |   294160582917 |
+----------------+----------------+----------------+

不过，我想另外说明一下:

• 以本地货币表示的 GDP，即使缺少汇率信息也是如此
• 在两列中使用本地货币“USD”的国家/地区的 GDP

期望的输出是:

+----------------+----------------+----------------+
| name           | GDP            | GDP US$        |
+----------------+----------------+----------------+
| European Union | 13900000000000 | 15444444853582 |
| Singapore      |   403000000000 |   294160582917 |
| Switzerland    |   163000000000 |                |
| USA            | 17400000000000 | 17400000000000 |
+----------------+----------------+----------------+

我将不胜感激。

Answer 1

使用 left join 包含缺少汇率的国家，并使用 case 表达式始终将 USA GDP 设置为 USA GDP:

SELECT 
    c.name, 
    GDP, 
    CASE WHEN c.name = 'USA' THEN c.GDP ELSE c.GDP/er.rate END AS 'GDP US$'
FROM countries c
LEFT JOIN exchange_rates er ON er.currency = c.currency;

此外，我更改为适当的连接并为表添加别名以稍微缩短查询。