count and sum the values sql
问题描述:
我有两张表:国家1,县2,国家边界,国家,年份和人口的边界,我想为每个国家列出其人口和所有邻国的总人口(如果它没有邻国,NULL) 到目前为止,我可以找到每个国家的最大人口以及每个国家有多少邻居,但我无法找到总结所有邻国人口的方法。有任何想法吗?count and sum the values sql
SELECT country, MAX(population) FROM COUNTRYPOPS GROUP BY 1 ORDER BY 1
---------------
SELECT countries, COUNT(countries) as have_borders
FROM
(SELECT country1 AS countries
FROM borders
UNION ALL
SELECT country2
FROM borders) t
GROUP BY countries
ORDER BY countries;
答
如何像
SELECT country, pop, SUM(cc.population) AS neighboursPop
FROM (
SELECT c.country AS country, c.population AS pop, b.country2 AS neighbour
FROM countrypops c
LEFT JOIN borders b ON b.country1 = c.country
) t
LEFT JOIN countrypops cc ON cc.country = t.neighbour
GROUP BY country;
没有测试它,但这个想法是。
UPD:或者它甚至可以被夷为平地像
SELECT country, pop, SUM(neighPop) AS neighboursPop
FROM (
SELECT
c.country AS country,
c.population AS pop,
b.country2 AS neighbour,
cc.population AS neighPop
FROM countrypops c
LEFT JOIN (borders b
LEFT JOIN countrypops cc ON cc.country = b.country2)
ON b.country1 = c.country
) t
GROUP BY country;
答
我认为相关子查询是最简单的方法:
select cp.country, cp.pop,
(select sum(cp2.pop)
from borders b join
countrypops cp2
on b.country2 = cp2.country
where b.country1 = cp.country
) as neighboring_pop
from countrypops cp;
这并不一定在外部查询的聚集,所以它应该比一种方法更有效率。
这是MySql还是SQL Server?请相应标记。 –
是'BORDERS'双向?例如。一行'美国','加拿大',另一行加'加拿大','美国',还是它只有一行(任意)方向? – Andreas
是的,这是双向的 – jack