没有窗口函数,MySQL应该怎么实现《分组排名》呢?

Python中文社区

共 1898字,需浏览 4分钟

 ·

2020-11-25 22:29

MySQL8.0以前,我们在MySQL中是不能使用窗口函数的,这就给我们书写分组排名语句造成了极大的难度,确实很不好写。鉴于一些粉丝的要求,他们说自己使用的MySQL不能使用窗口函数,但是又想要实现这样的功能,应该怎么办呢?下面这篇文章大家可以好好学学。

1.数据源

2.数据整体排名

1)普通排名

从1开始,按照顺序一次往下排(相同的值也是不同的排名)。

set @rank =0;
select 
 city ,
 score, 
 @rank := @rank+1 rank 
from cs 
order by score desc;

结果如下:

2)并列排名

相同的值是相同的排名(但是不留空位)。

set @rank=0,@price=null;
select cs.* ,
 case when @price = score then @rank 
 when @price := score then @rank := @rank+1 end rank  
 from cs order by score desc;
 -- 当查询的score 值 = @price时,输出@rank,
 -- 当不等时,将score值赋给@price ,并输出@rank := @rank+1
 
-- 或者
set @rank=0,@price=null;
select 
 a.city,a.score,a.rank 
from 
(select cs.*,
 @rank := if(@p=score,@rank,@rank+1rank,
 @p := score
from cs 
order by score desc) a;

结果如下:

3)并列排名

相同的值是相同的排名(但是留空位)。

set @rank=0,@price=null, @z=1;
select 
 a.city,a.score,a.rank 
from 
(select 
 cs.*,
 @rank := if(@p=score,@rank,@z) rank,
 @p := score,@z :=@z+1
from cs 
order by score desc) a;

结果如下:

3.数据分组后组内排名

1)分组普通排名

从1开始,按照顺序一次往下排(相同的值也是不同的排名)。

set @rank=0,@c=null;
select 
 cs.city,cs.score,
 @rank := if(@c = city,@rank+1,1rank,
 @c := city
from cs 
order by cs.city,cs.score;

结果如下:

2)分组后并列排名

组内相同数值排名相同,不占空位。

set @rank=0,@c=null,@s=null;
select 
 cs.city,cs.score,
 @rank := if(@c=city,if(@s=score,@rank,@rank+1),1rank ,
 @c := city,
 @s :=score 
from cs 
order by cs.city,cs.score;

结果如下:

3)分组后并列排名

组内相同数值排名相同,需要占空位。

set @rank=0,@z=0,@c=null,@s=null;
select 
 cs.city,cs.score,
 @z := if(@c=city,@z+1,1),
 @rank := if(@c=city,if(@s=score,@rank,@z),1rank,
 @c := city,
 @s :=score 
from cs 
order by cs.city,cs.score;

结果如下:

4.分组后取各组的前两名

① 方法一:按照分组排名的三种方式,然后限定排名的值
set @rank=0,@z=0,@c=null,@s=null;
select a.city,a.score,a.rank from 
(select 
 cs.city city,cs.score score,
 @z := if(@c=city,@z+1,1),
 @rank := if(@c=city,if(@s=score,@rank,@z),1rank,
 @c := city,
 @s :=score 
from cs 
order by cs.city,cs.score desc) a
where a.rank<=2;

结果如下:

② 内部查询
SELECT * FROM cs c
WHERE (
    SELECT count(*) FROM cs
    WHERE c.city=cs.city AND c.score2
ORDER BY city,score DESC;

结果如下:

上述代码的执行原理如下图:

     

推荐阅读



如何在 Matplotlib 中更改绘图背景


Python 解释器 PyPy 7.3.3 版本发布!


有人在代码里下毒!慎用 pip install 命令


点击下方阅读原文加入社区会员




点赞鼓励一下

浏览 37
点赞
评论
收藏
分享

手机扫一扫分享

分享
举报
评论
图片
表情
推荐
点赞
评论
收藏
分享

手机扫一扫分享

分享
举报