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

Question

我有三张桌子，一张是世界上每一个国家的，一张是象征性的

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
(...)

我现在要向那些山区比湖泊少的国家展示。我试了几个小时，但找不到解决这个问题的方法。我试着把这三张桌子连在一起--但我不知道下一步该怎么做。我确信我必须使用嵌套的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;

但不知何故，这两列的计数(Geo_lake.country)和计数(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