SQL - 每列的条件

Question

这是一个一般的 SQL 问题，但如果您必须知道数据库是 Firebird，我会告诉您。

我有这张表(已简化):

user  amount  type--------------------john  25      creditjohn  20      debitjohn  5       debitjohn  15      creditmike  15      credit

I want to have a result that looks like this, using a single query:

user  credit  debit--------------------john  40      25mike  15      NULL

Where credit = SUM(amount) WHERE type=credit
   and debit = SUM(amount) WHERE type=debit

基本上，我希望基于字段的 SUM(在本例中为金额)但在不同的条件(在本例中为类型)的结果上有多个字段。

提前感谢您的任何建议。

Answer 1

标准 SQL 是:

SELECT "user",
    SUM(CASE WHEN type='credit' then amount END) as Credit,
    SUM(CASE WHEN type='debit' then amount END) as Debit
FROM
    "table"
GROUP BY
    "user"