选择无限制的前N个值

Question

我有以下关系模式：

Country(code: Str, name: Str, capital: Str, area: int)
code (this is the usual country code, e.g. CDN for Canada, F for France, I for Italy)
name (the country name) capital (the capital city, e.g. Rome for Italy)
area (The mass land the country occupies in square Km)

Economy (country: Str, GDP: int, inflation: int, military: int, poverty: int)
country is FK  to the Country table
GDP (gross domestic product)
inflation (annual inflation rate)
military (military spending as percentage of the GDP)
poverty rate (percentage of population below the poverty line)  

Language (country: Str, language: Str, percentage: int)
country is FK  to the Country table
language is a spoken language name
percentage (percentage of population speaking the language)

我需要编写一个查询，查找使用语言最多的国家/地区的贫困率。

我编写了以下查询：

SELECT poverty
FROM(
  SELECT COUNT(language) as langnum, country
  FROM "Language"
  GROUP BY country) AS conto
JOIN "Economy" AS E
ON E.country=conto.country
ORDER BY conto.langnum DESC
LIMIT 1

如果只有一个国家拥有最多的语言，那么这个方法显然是有效的，但如果有多个国家都拥有最多的语言，我该怎么办呢？

- Eric Casera

---

非常好的问题，非常适合新成员。欢迎来到StackOverflow。 - Juan Carlos Oropeza

Answer 1

使用 rank() 或 dense_rank()：

SELECT poverty
FROM (SELECT COUNT(language) as langnum, country,
             RANK() OVER (ORDER BY COUNT(language) DESC) as ranking
      FROM "Language"
      GROUP BY country
     ) conto JOIN "Economy" AS E
     ON E.country=conto.country
WHERE conto.ranking = 1
ORDER BY conto.langnum DESC;

- Gordon Linoff

---

了解更多相关内容，请在此处阅读：http://www.sql-tutorial.ru/en/book_rank_dense_rank_functions.html - Juan Carlos Oropeza

非常好用！谢谢，我之前不知道rank()的存在。 - Eric Casera

Answer 2

只需添加另一个子查询，返回语言数量的最大值：

SELECT poverty, E.country
FROM(
  SELECT COUNT(language) as langnum, country
  FROM "Language"
  GROUP BY country) AS conto
JOIN "Economy" AS E
ON E.country=conto.country
JOIN (
SELECT COUNT(language) as langnum, country
  FROM "Language"
  GROUP BY country ORDER BY 1 DESC LIMIT 1
) AS maxlang ON maxlang.langnum = conto.langnum
ORDER BY conto.langnum DESC

- Jan