这篇博客主要探讨了在MySQL8.0中如何进行排序，包括使用开窗函数、联结法和用户变量解法。作者通过实例展示了row_number(), rank()和dense_rank()的运用，并解释了用户变量在排序中的工作原理。文章最后提到了对dense_rank()和rank()的用户变量实现方法的补充计划。"
112761959,7939230,轻松掌握Cookie设置技巧,"['前端开发', 'HTTP', '身份验证']
今日不加班，复习MySQL书部分章节，只做了一个题，排序这里比较复杂，明日周五，利用周末把MySQL8.0以下版本的排序方法学会。
这里仅是 分组排序，全局排序较简单。
问题14 按各科成绩进行排序，并显示排名
方法一：开窗函数
select sid, cid, sscore,
row_number() over(partition by cid order by sscore desc) as rank_rownumber,
rank() over(partition by cid order by sscore desc) as rank_rank,
dense_rank() over(partition by cid order by sscore desc) as rank_dense from score;
这里明显是不用开窗函数ntile()
运行结果：
+-----+-----+--------+----------------+-----------+------------+
| sid | cid | sscore | rank_rownumber | rank_rank | rank_dense |
+-----+-----+--------+----------------+-----------+------------+
| 01 | 01 | 80 | 1 | 1 | 1 |
| 03 | 01 | 80 | 2 | 1 | 1 |
| 05 | 01 | 76 | 3 | 3 | 2 |
| 02 | 01 | 70 | 4 | 4 | 3 |
| 04 | 01 | 50 | 5 | 5 | 4 |
| 06 | 01 | 31 | 6 | 6 | 5 |
| 01 | 02 | 90 | 1 | 1 | 1 |
| 07 | 02 | 89 | 2 | 2 | 2 |
| 05 | 02 | 87 | 3 | 3 | 3 |
| 03 | 02 | 80 | 4 | 4 | 4 |
| 02 | 02 | 60 | 5 | 5 | 5 |
| 04 | 02 | 30 | 6 | 6 | 6 |
| 01 | 03 | 99 | 1 | 1 | 1 |
| 07 | 03 | 98 | 2 | 2 | 2 |
| 02 | 03 | 80 | 3 | 3 | 3 |
| 03 | 03 | 80 | 4 | 3 | 3 |
| 06 | 03 | 34 | 5 | 5 | 4 |
| 04 | 03 | 20 | 6 | 6 | 5 |
+-----+-----+--------+----------------+-----------+------------+
其中，row_number仅生成连续的序号，rank排名存在跳跃，dense_rank排名不跳跃，紧跟上一序号。
方法二：联结解法
#实现rank()，排名有跳跃
SELECT a.cid,a.sid,a.sscore, count(b.sscore)+1 as ran
from score as a
left join
score as b
on a.sscore<b.sscore and a.cid=b.cid
group by a.cid,a.sid,a.sscore
#没有group by，聚集函数会作用到整张表上
order by a.cid,ran asc;
#实现dense_rank
select a.cid,a.sid,a.sscore, count(distinct(b.sscore)) as ran
from score as a
left join score as b
on a.sscore<=b.sscore and a.cid=b.cid
group by a.cid,a.sid,a.sscore
order by a.cid,ran;
dense_rank还可以用内联结，因为这里对比成绩用的<=
select a.cid,a.sid,a.sscore, count(distinct(b.sscore)) as ran
from score a
join score b
on a.sscore<=b.sscore and a.cid=b.cid
group by a.cid,a.sid,a.sscore
order by a.cid,ran;
方法三：用户变量解法（实现row_number）
#这两个解法实现的是row_number
select sid, cid,sscore, @ss,@tt,
if(@ss = cid, @tt :=@tt+1, @tt :=1) as rk,
@ss :=cid as cid_
from (select * from score order by cid,sscore desc) as t1
cross join
(select @ss :='', @tt :=0) as t2;
#方式2，order by放在外面
select sid, cid,sscore, @ss,@tt,
if(@ss = cid, @tt :=@tt+1, @tt :=1) as rk,
@ss :=cid as cid_
from score as t1,
(select @ss :='', @tt :=0) as t2
order by cid, sscore desc;
一开始不懂为什么这样能运行正确，就是score表不用排序，怎么能仅根据不相等来给排名逐渐加1，是不是order by的执行顺序在select前面了？
后来找资料得知：“用户变量表达式的执行顺序，与order和select关系不大。用户变量表达式的执行顺序实际上是先全表扫描，然后排序获得结果集，再给变量进行赋值。但是变量赋值的执行顺序没有保证，而且有可能随着版本而变化。”
https://www.zhihu.com/question/353177687/answer/876387714
待补充——dense_rank()+rank()的变量解法（3月9日补充）
方法三：用户变量解法：（实现dense_rank）
#if嵌套
select sid, cid, sscore,
if( @cidcc = cid,
if(@scorea = sscore, @drank, @drank:=@drank+1),
@drank:=1 ) as rk,
@scorea :=sscore, @cidcc :=cid
from score s,
(select @cidcc:=null,@scorea:=0, @drank:=0) t
order by cid, sscore desc;
运行结果：
+-----+-----+--------+------+------------------+--------------+
| sid | cid | sscore | rk | @scorea :=sscore | @cidcc :=cid |
+-----+-----+--------+------+------------------+--------------+
| 01 | 01 | 80 | 1 | 80 | 01 |
| 03 | 01 | 80 | 1 | 80 | 01 |
| 05 | 01 | 76 | 2 | 76 | 01 |
| 02 | 01 | 70 | 3 | 70 | 01 |
| 04 | 01 | 50 | 4 | 50 | 01 |
| 06 | 01 | 31 | 5 | 31 | 01 |
| 01 | 02 | 90 | 1 | 90 | 02 |
| 07 | 02 | 89 | 2 | 89 | 02 |
| 05 | 02 | 87 | 3 | 87 | 02 |
| 03 | 02 | 80 | 4 | 80 | 02 |
| 02 | 02 | 60 | 5 | 60 | 02 |
| 04 | 02 | 30 | 6 | 30 | 02 |
| 01 | 03 | 99 | 1 | 99 | 03 |
| 07 | 03 | 98 | 2 | 98 | 03 |
| 02 | 03 | 80 | 3 | 80 | 03 |
| 03 | 03 | 80 | 3 | 80 | 03 |
| 06 | 03 | 34 | 4 | 34 | 03 |
| 04 | 03 | 20 | 5 | 20 | 03 |
+-----+-----+--------+------+------------------+--------------+
方法三：用户变量解法（实现rank）
#试图用if嵌套但没做出来，这里用了两个if并列，
#分别对分组依据（课程cid字段）和排序依据（分数sscore字段）判断。
select sid, cid,sscore,@rowno:=@rowno+1 as hanghao,
if(@ccc = cid, @rankoffset := @rankoffset, @rankoffset :=@rowno-1) as offsetrank,
if(@scoreb =sscore, @currank, @currank :=@rowno) as isrankall,#全局排序
(@currank -@rankoffset) as isrank,#分组排序
@scoreb :=sscore, @ccc:=cid
from score s,
(select @rowno :=0,@ccc:=null,@currank:=0, @rankoffset :=0,@scoreb:=0) a
order by cid,sscore desc;
运行结果：
+-----+-----+--------+---------+------------+-----------+--------+------------------+-----------+
| sid | cid | sscore | hanghao | offsetrank | isrankall | isrank | @scoreb :=sscore | @ccc:=cid |
+-----+-----+--------+---------+------------+-----------+--------+------------------+-----------+
| 01 | 01 | 80 | 1 | 0 | 1 | 1 | 80 | 01 |
| 03 | 01 | 80 | 2 | 0 | 1 | 1 | 80 | 01 |
| 05 | 01 | 76 | 3 | 0 | 3 | 3 | 76 | 01 |
| 02 | 01 | 70 | 4 | 0 | 4 | 4 | 70 | 01 |
| 04 | 01 | 50 | 5 | 0 | 5 | 5 | 50 | 01 |
| 06 | 01 | 31 | 6 | 0 | 6 | 6 | 31 | 01 |
| 01 | 02 | 90 | 7 | 6 | 7 | 1 | 90 | 02 |
| 07 | 02 | 89 | 8 | 6 | 8 | 2 | 89 | 02 |
| 05 | 02 | 87 | 9 | 6 | 9 | 3 | 87 | 02 |
| 03 | 02 | 80 | 10 | 6 | 10 | 4 | 80 | 02 |
| 02 | 02 | 60 | 11 | 6 | 11 | 5 | 60 | 02 |
| 04 | 02 | 30 | 12 | 6 | 12 | 6 | 30 | 02 |
| 01 | 03 | 99 | 13 | 12 | 13 | 1 | 99 | 03 |
| 07 | 03 | 98 | 14 | 12 | 14 | 2 | 98 | 03 |
| 02 | 03 | 80 | 15 | 12 | 15 | 3 | 80 | 03 |
| 03 | 03 | 80 | 16 | 12 | 15 | 3 | 80 | 03 |
| 06 | 03 | 34 | 17 | 12 | 17 | 5 | 34 | 03 |
| 04 | 03 | 20 | 18 | 12 | 18 | 6 | 20 | 03 |
+-----+-----+--------+---------+------------+-----------+--------+------------------+-----------+
这个rank的答案是参考如下链接做出来的：
https://blog.youkuaiyun.com/zgdwxp/article/details/102696341
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
