今日不加班,复习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.csdn.net/zgdwxp/article/details/102696341