mysql - 如何从 'group by' 方法中查找前 N 条记录，其中 N 条记录可以包含多个值？

Question

我正在练习 W3 School SQL Tables .从 Customers 表中，我可以按照 SQL 选择 Number of Customers Per country

select country, count(*) as NumOfCustomer 
from Customers 
group by country
order by NumOfCustomer Desc;

这给了我这样的结果:

如果我想选择客户最多的前 5 个国家/地区，我不能使用 Limit 5，因为 Germany 和 France 的客户第二多，而 Mexico 和 Spain 拥有第五高的客户。使用限制 5 将不包括墨西哥和西类牙

如何获得包含所有 N 个最高值的结果，其中可以像以前一样为第 N 个数字重复最高值？

Answer 1

你可以使用 DENSE_RANK :

Returns the rank of the current row within its partition, without gaps. Peers are considered ties and receive the same rank. This function assigns consecutive ranks to peer groups; the result is that groups of size greater than one do not produce noncontiguous rank numbers

WITH cte AS (
  select country, count(*) as NumOfCustomer 
  from Customers 
  group by country
), cte2 AS (
  SELECT *, DENSE_RANK() OVER(ORDER BY NumOfCustomer DESC) AS rnk
  FROM cte
)
SELECT *
FROM cte2
WHERE rnk <= 5
ORDER BY NumOfCustomer DESC