oracle倒序排列语句_了解SQL语句顺序的重要性

了解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的原理细节了，之后有机会再来介绍。

好了，这就是本期的内容，喜欢我们文章的小伙伴，欢迎点赞、在看、转发。