首先我们创建一张city_popularity表：
CREATE TABLE city_popularity( region int(10) NOT NULL COMMENT ‘1 国内 2 海外‘, city_name VARCHAR(64) NOT NULL, popularity DOUBLE(5,2) NOT NULL);
并向其中添加数据：
INSERT INTO city_popularity (region, city_name, popularity) VALUES (1, ‘北京‘, 30.0), (1, ‘上海‘, 30.0), (1, ‘南京‘, 10.0), (2, ‘伦敦‘, 20.0), (1, ‘张家界‘, 8.0), (2, ‘纽约‘, 35.0), (1, ‘三亚‘, 25.0), (2, ‘新加坡‘, 35.0);
创建出的表及数据如下：
现在对所有城市的热门度进行排名：
1. 通过窗口函数
MySQL从8.0开始支持窗口函数，也叫分析函数，序号函数ROW_NUMBER(), RANK(), DENSE_RANK()满足不同需求的排序
SELECT region, city_name, popularity, ROW_NUMBER() OVER (PARTITION BY region ORDER BY popularity DESC) AS rank FROM city_popularity;
使用ROW_NUMBER()函数排序结果如下：
SELECT region, city_name, popularity, RANK() OVER (PARTITION BY region ORDER BY popularity DESC) AS rank FROM city_popularity;
使用RANK()函数排序结果如下：
SELECT region, city_name, popularity, DENSE_RANK() OVER (PARTITION BY region ORDER BY popularity DESC) AS rank FROM city_popularity;
使用DENSE_RANK()函数排序结果如下：
2. 通过表的自交
SELECT a.region, a.city_name, a.popularity, (COUNT(b.popularity)+1) AS rank FROM city_popularity AS a LEFT JOIN city_popularity AS b ON a.region = b.region AND a.popularity<b.popularity GROUP BY a.region, a.city_name, a.popularity ORDER BY a.region, rank;
以上通过表的自交实现了对国内和海外城市分别排序，且数据相同的情况，排名保持不变，且占有字符的排序：
3. 通过设置变量
SELECT city_popularity.*, @rank := @rank+1 AS rank FROM city_popularity ,(SELECT @rank:=0) init ORDER BY popularity DESC;
顺序排序，每多一条排序自增加一，结果如下：
select city_popularity.*, case when @popularity = popularity then @rank when @popularity := popularity then @rank :=@rank+1 when @popularity =0 then @rank :=@rank+1 END as rank from city_popularity,(select @rank :=0,@popularity :=NULL) init ORDER BY popularity DESC;
当数据相同时，排名一致，不相同则排名自增加一，结果如下：
select city_popularity.*, @rank1 :=@rank1+1,@rank := case when @popularity = popularity then @rank when @popularity := popularity then @rank1 when @popularity =0 then @rank1 END as rank from city_popularity,(select @rank :=0,@popularity :=NULL,@rank1 :=0) init ORDER BY popularity DESC;
数据相同的情况，排名保持不变，且占有字符，结果如下：
SELECT region, city_name, popularity, @rank:=@rank+1 AS rank FROM city_popularity, (SELECT @rank:=0) q ORDER BY popularity DESC;
参考链接：https://blog.csdn.net/justry_deng/article/details/80597916
https://blog.csdn.net/out_of_tune/article/details/90236270
原文：https://www.cnblogs.com/sweetttcareer/p/11959137.html