sql - 如何有条件地将某一列的不同值传递给聚合函数？

Question

好吧，这个问题的标题可能并不能真正反射(reflect)我想要的，我想不出更好的了。也许我应该在末尾附加一个“然后加入以这种方式获取的两个表”。

假设我有这张 table 。场景是，我们支付 ID 为 loan_id 的贷款，我们将付款存储在表“Payment”中，每笔付款都有自己的 id。当客户付款时，我们将paid更改为1。

+---------+----+--------+------+
| loan_id | id | amount | paid |
+---------+----+--------+------+
|       1 |  1 |   1000 |    1 |
|       1 |  2 |   1000 |    1 |
|       1 |  3 |   1000 |    0 |
|       2 |  4 |  10000 |    0 |
|       3 |  5 |  20000 |    1 |
+---------+----+--------+------+

现在我们需要一份贷款报告。输出应如下所示:

+---------+-------+-----------+
| loan_id |  paid | remaining |
+---------+-------+-----------+
|       1 |  2000 |      1000 |
|       2 |     0 |     10000 |
|       3 | 20000 |         0 |
+---------+-------+-----------+

paid 列基本上是所有 amount 字段的总和，其中 pay = 1。remaining 是 amount 的总和 pay = 0 的行。它们应该按 loan_id 分组。

我尝试了什么:

我尝试了不同的连接、产品等，但我能从查询中得到的最好的结果是这个:

SELECT loan_id, SUM(paid), SUM(remaining)
FROM (
    SELECT loan_id, 0 AS paid, SUM(amount) AS remaining
    FROM Payment
    WHERE paid = 0
    GROUP BY loan_id

    UNION

    SELECT loan_id, SUM(amount) AS paid, 0 AS remaining
    FROM Payment
    WHERE paid = 1
    GROUP BY loan_id
)
GROUP BY loan_id

但我想应该有比我更好的方法。

Answer 1

SELECT 
loan_id, 
SUM(case when paid = 1 then amount else 0 end) as payed, 
SUM(case when paid = 0 then amount else 0 end) as remaining
FROM Payment
GROUP BY loan_id

为了代码清晰，我会避免使用列名作为别名(付费 => 付费)