鉴于我有以下员工表：
| name | company |
|---------|----------|
| John | Tesco |
| Anna | Tesco |
| James | Shopo |
| Jessica | Salsbury |
以及他们的银行交易：
| seller | buyer | money |
|---------|---------|-------|
| John | James | 40 |
| James | Anna | 20 |
| James | Jessica | 30 |
| Jessica | James | 10 |
我想创建一个总结他们交易的新表
| company | incoming | outcoming |
|----------|----------|-----------|
| Tesco | 20 | 40 |
| Shopo | 50 | 50 |
| Salsbury | 30 | 10 |
但是我在这个GROUP_BY子句上苦苦挣扎，能够单独计算传入和传出，但无法提出一个可以同时计算两者的查询，而无需在SELECT内部运行额外的INNER JOIN
Aggregation 的一个典型Case .. When问题，使用：
select e.company,
sum(case when e.name = b.buyer then money end ) as incoming,
sum(case when e.name = b.seller then money end ) as outcoming
from employees e
left outer join bank_transactions b
on ( e.name in (b.seller, b.buyer ) )
group by e.company
order by e.company desc;
company incoming outcoming
Tesco 20 40
Shopo 50 50
Salsbury 30 10
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句