mysql查询排名前5的语句_MySQL语句实现排名

首先我们创建一张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);

创建出的表及数据如下：

现在对所有城市的热门度进行排名：

1. 通过窗口函数

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()函数排序结果如下：

2. 通过表的自交

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

以上通过表的自交实现了对国内和海外城市分别排序，且数据相同的情况，排名保持不变，且占有字符的排序：

3. 通过设置变量

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;

参考链接：https://blog.csdn.net/justry_deng/article/details/80597916

https://blog.csdn.net/out_of_tune/article/details/90236270