SQL：比较来自不同表的两个计数

Question

我有3张桌子，每个国家和地区都有它的代币

NAME              CODE
Afghanistan       AFG
Albania           AL
Algeria           DZ
American Samoa    AMSA
Andorra           AND
Angola            ANG
Anguilla          AXA
(...)

还有这些国家中所有湖泊和所有山峰中的一个的表。

LAKE                 CODE
Bodensee             A
Neusiedlersee        A
Lake Prespa          AL
Lake Ohrid           AL
Lake Skutari         AL
Lake Eyre            AUS
Lake Jindabyne       AUS
Lake Hume            AUS
Lake Eucumbene       AUS
Lake Hume            AUS
Lake Burley Griffin  AUS
(...)

MOUNTAIN             CODE
Hochgolling          A
Hochgolling          A
Zugspitze            A
Grossglockner        A
Jezerce              AL
Korab                AL
Uluru                AUS
Mt. Kosciuszko       AUS
Mt. Bogong           AUS
Musala               BG
Illampu              BOL
Sajama               BOL
Licancabur           BOL
(...)

我现在必须说明山峰少于湖泊的国家。我现在尝试了几个小时，但找不到解决此问题的方法。我试图将3个表连接在一起-
但我不知道下一步该怎么做。我确定我必须使用嵌套的SQL命令。

这是我对每个国家的湖泊和山脉计数的尝试

SELECT Country.name, count(Geo_lake.code), count(Geo_mountain.code) 
From Country 
INNER JOIN Geo_lake On (Country.code = geo_lake.code)
INNER JOIn Geo_mountain On (Country.code = geo_mountain.code) 
Group by Country.name;

但是以某种方式，两个列的count（Geo_lake.country）和count（Geo_mountain.country）似乎都具有相同的值，我不知道为什么。

Answer 1

首先获取每个人的总和：

# Sum of lakes
SELECT code, count(*) AS sum FROM lakes GROUP BY code

# Sum of mountains
SELECT code, count(*) AS sum FROM mountains GROUP BY code

然后将结果合并在一起，并选择一个国家的山之和小于湖泊数量的所有行：

SELECT l.code AS code, l.sum AS lake_count, m.sum AS mountain_count
FROM
(SELECT code, count(*) AS sum FROM lakes GROUP BY code) AS l JOIN
(SELECT code, count(*) AS sum FROM mountains GROUP BY code) AS m
ON l.code = m.code
WHERE m.sum < l.sum