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