典型的 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

sql fiddle演示

我认为解决这类问题的关键是将 transactions 分为传入和传出。它使用 union all . 一旦你做了这些，剩下的就成问题了 join 以及 group by :

with tt as (
      select t.seller as employee, 0 as incoming t.money as outgoing 
      from transactions t
      union all
      select t.buyer as employee, t.money as incoming 0 as outgoing 
      from transactions t
     )
select e.company, sum(incoming) as incoming, sum(outgoing) as outgoing
from tt join
     employees e
     on t.employee = e.name 
group by e.company;

首先让我们了解这个问题。假设您的第一个表是'employees'，第二个表是bank\u transactions。卖方和买方都在第二张table上。卖方和买方都是第一表中任何一家公司的雇员。因此，让我们复制第二个表并将其保留为两个，一个用于买家，另一个用于别名为的卖家。
b类银行交易——买家
卖方的银行交易
所以现在混乱解决了，卖家会给他们的公司收入，买家会产生结果（支出）。

select e.company as company,
    sum(s.money) as income,
    sum(b.money) as outcome 
    from
    employees e
    left outer join
    bank_transactions s
    on(e.name=s.seller)
    left outer join
    bank_transactions b
    on(e.name=b.buyer)
    group by e.company