[LeetCode]Rank Scores,解题报告

来源：互联网发布：日常口语话题topic知乎编辑：程序博客网时间：2024/05/16 00:38

题目

Write a SQL query to rank scores. If there is a tie between two scores, both should have the same ranking. Note that after a tie, the next ranking number should be the next consecutive integer value. In other words, there should be no “holes” between ranks.

IdScore13.5023.6534.0043.8554.0063.65

For example, given the above Scores table, your query should generate the following report (order by highest score):

ScoreRank4.0014.0013.8523.6533.6533.504

思路

官方讨论里需要用到MYSQL的用户自定义变量了，但是我没用哈，我用的是笛卡尔乘积。

笛卡尔乘积

笛卡尔乘积其实就是两个表的级联，所以接下来我分析一下具体的步骤：

选择所有不重复的Score数据，组成新表s2。

<code class="language-sql hljs  has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background: transparent;">(<span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">select</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">distinct</span> Score <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">from</span> Scores) <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">as</span> s2;</span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li></ul>

级联Scores表和s2表，级联的条件是Scores表的Score字段值小于等于s2表Score字段的值。

<code class="language-sql hljs  has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background: transparent;"><span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">select</span> * <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">from</span> Scores <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">as</span> s1 <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">left</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">join</span> (<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">select</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">distinct</span> Score <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">from</span> Scores) <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">as</span> s2 <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">on</span> s1.Score <= s2.Score;</span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li></ul>

这样级联后，生成的新表内容如下：

s1.Scoreids2.Score3.513.53.513.653.513.853.514.03.6523.653.6523.853.6524434

这里只举了前三个数据的例子，通过这个临时表，其实我们应该已经能得出解决思路了。我们接下来，可以通过先用id字段做聚集，然后使用count(s2.Score)的数量作为Rank字段。

排序数据(AC SQL)。

<code class="language-sql hljs  has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background: transparent;"><span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">select</span> s1.Score <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">as</span> Score, <span class="hljs-aggregate" style="box-sizing: border-box;">count</span>(s2.Score) <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">as</span> Rank <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">from</span> Scores <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">as</span> s1 <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">left</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">join</span> (<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">select</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">distinct</span> Score <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">from</span> Scores) <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">as</span> s2 <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">on</span> s1.Score <= s2.Score <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">group</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">by</span> s1.id <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">order</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">by</span> s1.Score <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">desc</span>;</span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li></ul>

用户自定义变量

自定义变量概述

用户自定义的变量可以先在用户变量中保存值，然后再以后引用它。这样，可以将值从一个语句传递到另一个语句。用户变量与连接有关，也就是说，一个客户端定义的变量不能被其它客户端看到或使用。当客户端退出时，该客户端连接的所有变量都将自动释放。

自定义变量语法

在select语句中赋值给用户变量的语法是：@var_name := value，这里的var_name是变量名，value是你正在检索的值。

AC SQL

<code class="language-sql hljs  has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background: transparent;"><span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">select</span> Score, Rank <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">from</span> (<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">select</span> Score, @curRank := @curRank + <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">IF</span>(@prevScore = Score, <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0</span>, <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1</span>) <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">as</span> Rank, @prevScore := Score <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">from</span>Scores <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">as</span> s, (<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">select</span> @curRank := <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0</span>) <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">as</span> r, (<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">select</span> @prevScore := <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">NULL</span>) <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">as</span> p<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">order</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">by</span> Score <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">DESC</span>) <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">as</span> t;</span></code>

0 0