如何在每组mysql查询中获得第n个最高条目

I am a beginner in MySQL and I've stumbled upon this for the past few days now.

我是MySQL的初学者，过去几天我偶然发现了这个问题。

Let's say I have a country table as seen below:

假设我有一个国家/地区表，如下所示：

code    name            continent       Population
ABW Aruba               North America   103000
AFG Afghanistan         Asia            22720000
AGO Angola              Africa          12878000
AIA Anguilla            North America   8000
ALB Albania             Europe          3401200
AND Andorra             Europe          78000
ARG Argentina           South America   37032000

This table has information of continents and population of each country.

该表包含每个国家的大陆和人口信息。

How do I find the country with nth highest population in each continent?

我怎样才能找到每个大陆人口最多的国家？

For a specific scenario, How do I find the country with the thrid highest population in each continent?

对于特定情景，我如何找到每个大陆中人口最多的国家？

I checked many SO questions, including this link. But could not find the answer for this problem. Any help would be greatly appreciated!

我查了很多SO问题，包括这个链接。但是找不到这个问题的答案。任何帮助将不胜感激！

3 个解决方案

#1

One option to do this is using variables.

执行此操作的一个选项是使用变量。

select code,name,continent,population 
from (
select c.*, 
@prevPopulation:=@curPopulation,
@curPopulation:=population,
@prevContinent:=@curContinent,
@curContinent:=continent,
case when @curContinent = @prevContinent and @prevPopulation <> @curPopulation then @rn:=@rn+1
     when @curContinent = @prevContinent and @prevPopulation = @curPopulation then @rn:=@rn
else @rn:=1 end as rank
from country c,
(select @rn := 0, @curContinent := '',@prevContinent := '', @curPopulation:=0,@prevPopulation:=0) r
order by continent,population desc
) x
where rank = 3 --replace it with the nth highest value needed

This query uses 4 variables

此查询使用4个变量

1) @curContinent which is initially set to an empty string. Thereafter the select assigns the current row's continent.

1）@curContinent，最初设置为空字符串。此后，选择分配当前行的大陆。

2) @prevContinent which is initially set to an empty string. Thereafter the select sets it to @curContinent value (which is an empty string initially).

2）@prevContinent，最初设置为空字符串。此后，select将其设置为@curContinent值（最初为空字符串）。

3) @curPopulation which is initially set to 0. Thereafter the select assigns the current row's population.

3）@curPopulation最初设置为0.此后select选择当前行的总体。

4) @prevPopulation which is initially set to 0. Thereafter the select sets it to the @curPopulation (which is 0 for the first time and so on).

4）@prevPopulation最初设置为0.此后，select将其设置为@curPopulation（第一次为0，依此类推）。

order by clause is important here to desingate the current and previous rows based on continent and population. This would handle ties too as it would assign the same rank to all the countries within a continent with the same population.

order by子句对于根据大陆和人口设计当前和以前的行非常重要。这也可以处理关系，因为它会为具有相同人口的大陆中的所有国家分配相同的等级。

Run the inner query initially to see how the variables are set, which would clarify things for you.

最初运行内部查询以查看变量的设置方式，这将为您澄清一些事情。

Sample Demo

示例演示

#2

Assuming no countries have the same population, then one method is to count the number of countries that have the same or higher population and see when the count is 3:

假设没有国家拥有相同的人口，那么一种方法是计算拥有相同或更高人口的国家数量，并查看计数何时为3：

select c.*
from country c
where (select count(*)
       from country c2
       where c2.continent = c.continent and c2.population >= c.population
      ) = 3;

#3

      with result as 
      (
        select code,name,continent,population,dense_rank() over( order by population ) as rnk 
        from dbo.country
       )
      select population,continent from result where rnk=3 group by continent,population  ;

If you want the 2nd highest population then enter the rnk in where clause as 2 and so on ..

如果你想要第二高的人口，那么在where子句中输入rnk为2，依此类推。

#1