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

首先获取每个人的总和：

# Sum of lakesSELECt pre, count(*) AS sum FROM lakes GROUP BY pre# Sum of mountainsSELECt pre, count(*) AS sum FROM mountains GROUP BY pre

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

SELECt l.pre AS pre, l.sum AS lake_count, m.sum AS mountain_countFROM(SELECt pre, count(*) AS sum FROM lakes GROUP BY pre) AS l JOIN(SELECt pre, count(*) AS sum FROM mountains GROUP BY pre) AS mON l.pre = m.preWHERe m.sum < l.sum