[Leetcode] Rank Scores--database knowledge (MySQL)

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

+----+-------+|Id|Score|+----+-------+| 1  | 3.50  || 2  | 3.65  || 3  | 4.00  || 4  | 3.85  || 5  | 4.00  || 6  | 3.65  |+----+-------+

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

+-------+------+|Score|Rank|+-------+------+| 4.00  | 1    || 4.00  | 1    || 3.85  | 2    || 3.65  | 3    || 3.65  | 3    || 3.50  | 4    |+-------+------+

Solution:

1. With Variables (user-defined variable): ms

First one uses the variables, one for the current rank and one for the previous score.

SELECTScore,@rank:= @rank +(@prev <>(@prev:=score)) Rank fromScores, (SELECT @rank:= 0,@prev:= -1) InitORDER  byScoredesc

The main idea of this approach is based on variables. The key point is to select the rank and the previous score two variables, initialize the variables, and judge the rank of the same score. MySQL is not like SQL has 4 ranking functions can be called, so you have to write the function of ranking.

A similar implementation:

SELECTScore, Rank from(  SELECTScore,@curRank:= @curRank + IF(@prevScore =Score,0,1) asRank,@prevScore:=score fromScores S, (SELECT @curRank:= 0) R, (SELECT @prevScore:= NULL) PORDER  byScoreDESC) T;

2. Always Count: 1322 ms

This one counts, for each score, the number of distinct greater or equal scores.

SELECT   score,  (SELECTcount(distinctfromWHERE>=  s.score) Rank from Scores sORDERbydesc

Not much to understand the idea of this algorithm.

Additional knowledge: Mysql user-defined variable details

You can use SQL statements to store values in user-defined variables, and then use another SQL statement to query user-defined variables. In this way, values can be passed between different SQL.

The declaration method of a user-defined variable is @var_name, where the variable name consists of a letter, a number, a ".", "_", and "$". Of course, you can also include other characters (for example: @ ' My-var ', @ "My-var", or @ ' My-var ') when referring to a string or identifier.

User-defined variables are session-level variables. The scope of its variables is limited to the client link that declares it. When this client disconnects, all of its session variables will be freed. User-defined variables are case-insensitive.

Use the SET statement to declare a user-defined variable:

1 SET @var_name = expr[, @var_name = expr] ...

When setting a variable with set, you can use the "=" or ": =" operator to assign a value. Of course, there are other ways to assign values in addition to the SET statement. For example, the assignment operator can only use ": =". Because the "=" operator will be considered a comparison operator.

Mysql> SET @t1 =1, @t2 =2, @t3:=4;mysql> SELECT @t1, @t2, @t3, @t4: = @[email protected][email protected];

[Leetcode] Rank Scores--database knowledge (MySQL)