[Leetcode] Rank Scores, Problem solving report

Topics

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

Ideas

The official discussion requires MySQL user-defined variables, but I'm useless, I'm using the Cartesian product. Cartesian product

The Cartesian product is actually a cascade of two tables, so next I'll analyze the specific steps: Select all the non-repeating score data to form the new table S2.

(select DISTINCT score from Scores) as S2;

Cascading scores tables and S2 tables, the cascading condition is that the score field value of the scores table is less than or equal to the value of the S2 table score field.

SELECT * from Scores as S1 left joins (select distinct score from Scores) as S2 on S1. Score <= S2. Score;

After this cascade, the new table content is generated as follows:

S1. Score	ID	s2. Score
3.5	1	3.5
3.5	1	3.65
3.5	1	3.85
3.5	1	4.0
3.65	2	3.65
3.65	2	3.85
3.65	2	4
4	3	4

Here are just a few examples of the first three data, and through this temporary table, we should have been able to come up with a solution. Next, we can do this by first aggregating with the ID field and then using count (S2. Score) Number as the Rank field. Sort data (AC SQL).

Select S1. Score as score, count (S2. Score) as Rank from Scores as S1 left join (select DISTINCT score from Scores) as S2 on S1. Score <= S2. Score GROUP BY S1.id order by S1. Score desc;

user-defined variables Overview of custom variables

A user-defined variable can first save a value in a user variable and then reference it later. This allows you to pass a value from one statement to another. User variables are related to connections, meaning that a client-defined variable cannot be seen or used by other clients. When the client exits, all the variables that the client connects to are automatically freed. Custom Variable Syntax

The syntax for assigning a value to a user variable in a SELECT statement is: @var_name: = value, where var_name is the variable name and value is what you are retrieving. AC SQL

Select Score, rank from (
Select Score, @curRank: = @curRank + IF (@prevScore = score, 0, 1) as Rank, @prevScore: = Sco Re from
Scores as S, (select @curRank: = 0) as r, (select @prevScore: = NULL) as P
order by score DESC
) as T