同一张表上的MySQL多子查询

Question

我有一个如下结构的表

ID | Amount | Bank (1 or 2)
---+--------+------
1  | 100000 | 1
2  | 256415 | 2
3  | 142535 | 1
1  | 214561 | 2
2  | 123456 | 1
1  | 987654 | 2

我想要这样的结果(来自同一张表):

ID | sum(Bank 1) | sum(Bank 2)
---+-------------+------------
1  | 100000      | 1202215
2  | 123456      | 256415
3  | 142535      | 0

实现此目标最简单的查询是什么？

Answer 1

ANSI 解决方案:

Select Id
  , Sum( Case When Bank = 1 Then Amount End ) As Bank1Total
  , Sum( Case When Bank = 2 Then Amount End ) As Bank2Total
From SourceTable
Group By Id

SQL Fiddle Version (使用 MySQL)