当前位置：文江博客话题详情

如何在SQL中使用左JOIN来提取结果？

发布于 2025-02-13 07:03:48 字数 973 浏览 2 评论 0原文

任务是生成一个摘要，该摘要由每个国家进口和出口的商品价值组成。请注意，当一家公司购买一些商品时，它会导致其国家的总进口，当公司出售某些商品时，它会促成其国家的总出口。

编写由三列组成的退货表的SQL查询：国家，进出口，进口，其中包含每个国家的出口和进口商品价值的总和。每个国家都应出现在此表中，结果应按国家越来越多地排序。

两个表如下：

查询应返回：

假设：

单个国家内的公司之间没有贸易。
餐桌交易中的每个公司也出现在餐桌公司中。
每个公司都完全出现在餐桌公司中一次。

我尝试了以下查询，但没有返回正确的结果，如上所述。

SELECT cd.country, SUM(exports.value) AS export, SUM(imports.value) AS import
  FROM companies cd 
  LEFT JOIN trades exports
    ON cd.country= exports.seller
  LEFT JOIN trades imports
    ON cd.country = imports.buyer
  GROUP BY cd.country
  ORDER BY cd.country

原文

The task is to generate a summary that consists of sums of the value of goods imported and exported by every country. Note that when a company buys some goods, it contributes to its country's total import, and when the company sells some goods, it contributes to its country's total export.

Write SQL query that return table consisting of three columns: country, export, import, which contain the sums of the values of the exported and imported goods for every country. Each country should appear in this table and result should be sorted increasingly by country.

Two tables are as follows:

The query should return:

Assume that:

There is no trade between companies within a single country.
Every company in table trades also appears in table companies.
Every company appears in table companies exactly once.

I have tried the following query but it isn't returning the correct result as shown above.

SELECT cd.country, SUM(exports.value) AS export, SUM(imports.value) AS import
  FROM companies cd 
  LEFT JOIN trades exports
    ON cd.country= exports.seller
  LEFT JOIN trades imports
    ON cd.country = imports.buyer
  GROUP BY cd.country
  ORDER BY cd.country

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

音盲 2025-02-20 07:03:48

一个简单的解决方案是分别计算出口并将其组合在一起：

select country, min(export) as export, min(import) as import
from (
    select companies.country, sum(value) as export, null as import
    from trades
    join companies on trades.seller = companies.name
    group by companies.country

    union all

    select companies.country, null, sum(value)
    from trades
    join companies on trades.buyer = companies.name
    group by companies.country
) as x
group by country

A simple solution is calculate exports and imports separately and combine them:

select country, min(export) as export, min(import) as import
from (
    select companies.country, sum(value) as export, null as import
    from trades
    join companies on trades.seller = companies.name
    group by companies.country

    union all

    select companies.country, null, sum(value)
    from trades
    join companies on trades.buyer = companies.name
    group by companies.country
) as x
group by country

回复收藏 0 原文

~没有更多了~