在表之间共享值的SQL Sum行

I have two tables:

我有两张桌子:

Country
|-----------------|
|Code | Population|
|-----------------|
|  1  |    10     |
|-----------------|
|  2  |    20     |
|-----------------|
|  3  |    30     |
|-----------------|

and

Borders
|---------------------|
| country1 | country2 |
|---------------------|
|    1     |    3     |
|---------------------|
|    1     |    2     |
|---------------------|
|    2     |    1     |
|---------------------|

Explanation of tables: Country with code 1 has population of 10, country with code 2 has population of 20, country with code 3 has population of 30.

表格说明:代码为1的国家/地区的人口为10,代码为2的国家/地区的人口为20,代码为3的国家/地区的人口为30。

Country with code 1 borders country 3, country with code 1 also borders country 2. Country with code 2 borders country 1.

代码为1的国家/地区为国家/地区3,代码为1的国家/地区也与国家/地区相对。国家/地区代码为2

How do I find the TOTAL population of each country's bordering countries? For example, the total population of country 1's bordering countries would be 30 + 20, or 50, because it borders country 3 and country 2.

我如何找到每个国家的边境国家的总人口?例如,国家1的边界国家的总人口将是30 + 20或50,因为它与国家3和国家2接壤。

I've tried using joins and sums, but I still do not know how to find this value for each country. Any ideas? Thanks!

我尝试过使用连接和总和,但我仍然不知道如何为每个国家找到这个值。有任何想法吗?谢谢!

2 个解决方案

#1

Try this:

SELECT c1.Code, SUM(c2.Population)
FROM Country AS c1
INNER JOIN Borders AS b ON c1.Code = b.country1 
INNER JOIN Country AS c2 OB b.country2 = c2.Code
GROUP BY c1.Code

#2

Join the two tables

加入两个表

SELECT b.country1, SUM(c.population) AS Population
FROM country c
LEFT JOIN borders b ON c.code = b.country2
GROUP BY b.country1

And the output would be

输出就是

-------------------------------------------
| Code       | Population                 |
-------------------------------------------
| 1          | 50                         |
-------------------------------------------
| 2          | 10                         |
-------------------------------------------

#1