使用 CASE WHEN 进行 SQL 查询以透视列

Question

我有下表：

Bank:
name  val   amount
John   1     2000
Peter  1     1999
Peter  2     1854
John   2     1888

我正在尝试编写一个 SQL 查询来给出以下结果：

name  amountVal1 amountVal2  
John    2000        1888   
Peter   1999        1854    

到目前为止我有这个：

SELECT name,
CASE WHEN val = 1 THEN amount ELSE 0 END AS amountVal1,
CASE WHEN val = 2 THEN amount ELSE 0 END AS amountVal2
FROM bank

但是，它给出了稍微错误的结果：

name  amountVal1 amountVal2  
John    2000        0
Peter   1999        0
John    0          1888   
Peter   0          1854    

如何修改我的查询以给出正确的表示？ 谢谢

Answer 1

SELECT 
  name,
  SUM(CASE WHEN val = 1 THEN amount ELSE 0 END) AS amountVal1,
  SUM(CASE WHEN val = 2 THEN amount ELSE 0 END) AS amountVal2
FROM bank GROUP BY name

Answer 2

看起来您需要将表本身加入进来。试试这个：

select bank1.name, bank1.amount, bank2.amount
from bank bank1
inner join bank bank2 on 
    bank1.name = bank2.name 
    and bank1.val = 1 
    and bank2.val = 2