sql - 集团公司及其交易

Question

我试图在 SQL 中找出一个国家的公司之间的交易数量：

这是我的桌子公司和优惠

Company
================
companyname     country
A               US
B               US
C               UK
D               Russia  


Deals
================
id  sold bought total
1   A    B      50
2   B    C      100
3   C    A      150
4   D    B      200


Result:
============
country     sold    bought
US          150     200
UK          150     100
Russia      200     0

我尝试了以下仅获取已售商品的查询：

select country, sum(total) as sold 
from company c left join deals d on c.companyname=d.sold 
group by country.

如何编写查询以一次获得买卖结果？

标签： sqljoin

Answer 1

一种方法使用子查询

select country, sum(sold) as sold, sum(bought) as bought
from (select c.*,
             (select sum(d.total)
              from deals d
              where d.sold = c.companyname
             ) as sold,
             (select sum(d.total
              from deals d
              where d.bought = c.companyname
             ) as bought
      from company c
     ) c
group by country;