首先我们创建一张city_popularity表：
CREATE TABLEcity_popularity(
regionint(10) NOT NULL COMMENT ‘1 国内 2 海外’,
city_nameVARCHAR(64) NOT NULL,
popularityDOUBLE(5,2) NOT NULL);
并向其中添加数据：
INSERT INTOcity_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);
创建出的表及数据如下：
现在对所有城市的热门度进行排名：
- 通过窗口函数
MySQL从8.0开始支持窗口函数，也叫分析函数，序号函数ROW_NUMBER(), RANK(), DENSE_RANK()满足不同需求的排序
SELECTregion, city_name, popularity,
ROW_NUMBER()OVER (PARTITION BY region ORDER BY popularity DESC) ASrankFROM city_popularity;
使用ROW_NUMBER()函数排序结果如下：
SELECTregion, city_name, popularity,
RANK()OVER (PARTITION BY region ORDER BY popularity DESC) ASrankFROM city_popularity;
使用RANK()函数排序结果如下：
SELECTregion, city_name, popularity,
DENSE_RANK()OVER (PARTITION BY region ORDER BY popularity DESC) ASrankFROM city_popularity;
使用DENSE_RANK()函数排序结果如下：
- 通过表的自交
SELECT a.region, a.city_name, a.popularity, (COUNT(b.popularity)+1) ASrankFROM city_popularity AS a LEFT JOIN city_popularity ASbON a.region = b.region AND a.popularity
以上通过表的自交实现了对国内和海外城市分别排序，且数据相同的情况，排名保持不变，且占有字符的排序：
- 通过设置变量
SELECT city_popularity.*,@rank := @rank+1 ASrankFROM city_popularity ,(SELECT @rank:=0) initORDER 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 asrankfrom city_popularity,(select @rank :=0,@popularity :=NULL) initORDER 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 asrankfrom city_popularity,(select @rank :=0,@popularity :=NULL,@rank1 :=0) initORDER BY popularity DESC;
数据相同的情况，排名保持不变，且占有字符，结果如下：
SELECT region, city_name, popularity, @rank:=@rank+1 ASrankFROM city_popularity, (SELECT @rank:=0) q ORDER BY popularity DESC;
