sql - 如何为这种情况编写查询

Question

我在 PostgreSQL 中有一个如下表。

+-------+--------+----------+--------+
|id     | name   |  rate    | amount |
+-------+--------+----------+--------+
|123    | Flat   |          | 40     |
|123    | weekly | 200      |        |
+-------+--------+----------+--------+

我需要在“名称”列中显示持平时的金额和每周显示的费率。期望这样的输出:

+-------+-------+-----------+--------+
| id    | name  |  rate     | amount |
+-------+-------+-----------+--------+
| 123   | 40    |           | 40     |
| 123   | 200   | 200       |        |
+-------+-------+-----------+--------+

在 PostgreSQL 中是否可行。如果是，怎么做。

Answer 1

使用 CASE表达式:

SELECT id, CASE WHEN name = 'Flat' THEN amount ELSE rate END AS name, rate, amount
FROM mytable