了解SQL语句顺序的重要性
学习编程必须要真真实实地写代码才能真正把它变成自己的东西,免不了要去刷LeetCode。SQL作为使用频率最高的语言,当然需要更好的练习。在LeetCode的SQL题库里有这么一道题,看起来很好解决,实际上动手会出现各种各样的问题,通过这道题也是理解SQL执行顺序的一个好案例,话不多说,我们先来看看这道题的说明。
题目是LeetCode练习第178题,见:https://leetcode-cn.com/problems/rank-scores/
鉴于LeetCode执行速度较慢建议复制到本地运行,当然这样就使用不了LeetCode的测试用例。
建表SQL
Create table If Not Exists Scores (Id int, Score DECIMAL(3,2));
Truncate table Scores;
insert into Scores (Id, Score) values ('1', '3.5');
insert into Scores (Id, Score) values ('2', '3.65');
insert into Scores (Id, Score) values ('3', '4.0');
insert into Scores (Id, Score) values ('4', '3.85');
insert into Scores (Id, Score) values ('5', '4.0');
insert into Scores (Id, Score) values ('6', '3.65');
描述
编写一个 SQL 查询来实现分数排名。
如果两个分数相同,则两个分数排名(Rank)相同。请注意,平分后的下一个名次应该是下一个连续的整数值。换句话说,名次之间不应该有“间隔”。
+----+-------+
| Id | Score |
+----+-------+
| 1 | 3.50 |
| 2 | 3.65 |
| 3 | 4.00 |
| 4 | 3.85 |
| 5 | 4.00 |
| 6 | 3.65 |
+----+-------+
例如,根据上述给定的 Scores
表,你的查询应该返回(按分数从高到低排列):
+-------+------+
| Score | Rank |
+-------+------+
| 4.00 | 1 |
| 4.00 | 1 |
| 3.85 | 2 |
| 3.65 | 3 |
| 3.65 | 3 |
| 3.50 | 4 |
+-------+------+
这里用一些会绕些弯的SQL实现,对一列排序一般情况下使用窗口函数就可以很容易做到
select Score,dense_rank() over(order by Score desc) `Rank` from Scores;
只用一行SQL就可以实现题目的要求
在MySQL的8.0之前是不支持窗口函数的,这就损失了一个非常好的工具,但是我们可以通过使用变量的方法实现排序,只不过复杂了一些
首先我们需要得到倒序的Score
select score from scores order by score desc;
这里没有什么难度,接下来我们要得到他的排序,从题目中的提示可以知道,只有当当前的Score小于前一个Score的时候Rank+1才行,因为我们上面已经对Score进行了倒序排列,在这里只要上一个Score不等于当前的Score,Rank就+1。
使用case when或者if都可以实现这点
select t.score score,@rank:= if(@pre=t.score,@rank,@rank+1) `rank`,@pre:=t.score pre
from (select score from scores order by score desc) t
注:在MySQL中:=
表示赋值=
表示判断,在MySQL中声明变量使用@
符号实现,@rank
表示score的排名,@pre
表示上一个score。因为rank是MySQL 的保留字,使用"`"来转义
如果用上面的语句再运行一遍会得到从5开始的排序。很明显MySQL 的rank自增了,换种说法就是变量保留了下来。就像python一样的编程语言,这里就需要重新赋值。可以把赋值语句写在SQL语句中,也可以先初始化再运行SQL语句
set @pre:=null,@rank:=0
select t.score score,@rank := if(@pre=t.score,@rank,@rank+1) `rank`,@pre:=t.score pre
from (select score from scores order by score desc) t,(select @pre:=null,@rank:=0) a
最后再套个壳
set @pre:=null,@rank:=0;
select score Score,`rank` `Rank`
from (select t.score score,@rank:= if(@pre=t.score,@rank,@rank+1) `rank`,@pre:=t.score pre
from (select score from scores order by score desc) t) b
select score Score,`rank` `Rank`
from (select t.score score,@rank:=if(@pre=t.score,@rank,@rank+1) `rank`,@pre:=t.score pre
from (select score from scores order by score desc) t,(select @pre:=null,@rank:=0) a) b;
从开头到现在好像没有什么问题,如果把一些东西调换一些顺序就会有些不同。我们知道MySQL的语句执行时是有一个过程的。
(8) SELECT (9) DISTINCT (11)
(1) FROM
(3) JOIN
(2) ON
(4) WHERE
(5) GROUP BY
(6) WITH {CUBE | ROLLUP}
(7) HAVING
(10) ORDER BY
编程语言一向重视逻辑,简单来讲如果你把球打进了草丛里,你要找球,应该怎么找?正确的方法是从草丛的一边找到草丛的另一边,看起来很简单,但确实很重要。
做个小改动,把@pre
放到前面去
select score Score,`rank` `Rank`
from (select @pre:=t.score pre,t.score score,@rank:=if(@pre=t.score,@rank,@rank+1) `rank`
from (select score from scores order by score desc) t,(select @pre:=null,@rank:=0) a) b;
得到的结果全是0
+-------+------+
| Score | Rank |
+-------+------+
| 4.00 | 0 |
| 4.00 | 0 |
| 3.85 | 0 |
| 3.65 | 0 |
| 3.65 | 0 |
| 3.50 | 0 |
+-------+------+
因为找这个问题倒是花了我不少时间,以前练习变量排序的时候从没遇到问题,就单单在这遇上了滑铁卢。
因为@pre
提前了,@pre
的值永远都等于当前的score,@rank
的值永远等于0,只要@pre
的赋值在@rank
之前,结果都是这样,但只在score之前不会出现。根据这个案例,数据库读取数据的时候应该会先读一行数据,再进行判断打印。
再深入的话可能就是MySQL的原理细节了,之后有机会再来介绍。
好了,这就是本期的内容,喜欢我们文章的小伙伴,欢迎点赞、在看、转发。