MySQL语句实现排名

 

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