付款表
| 顾客 | 支付方式 | 量 |
|---|---|---|
| 彼得 | 信用 | 100 |
| 彼得 | 信用 | 300 |
| 约翰 | 信用 | 1000 |
| 约翰 | 借方 | 500 |
select customer, sum(case when payment_type = 'credit' then amount else 0 end) as credit, sum(case when payment_type = 'debit' then amount else 0 end) as debit from payments group by customer
结果：
| 顾客 | 信用 | 借方 |
|---|---|---|
| 彼得 | 400 | 0 |
| 约翰 | 1000 | 500 |
select customer, sum(case when payment_type = 'credit' then 1 else 0 end) as credit_transaction_count, sum(case when payment_type = 'debit' then 1 else 0 end) as debit_transaction_count from payments group by customer
结果：
| 顾客 | credit_transaction_count | debit_transaction_count |
|---|---|---|
| 彼得 | 2 | 0 |
| 约翰 | 1 | 1 |