Leetcode:rank Scores-Rank by score

1. Title

Rank Scores (number of times by score)

2. Address of the topic

https://leetcode.com/problems/rank-scores/

3. Topic content

Rank by score, if the score of two ID is the same, then their rank is the same, the ranking starts from 1. Note that the rank of each group of scores is a number of previous components plus one.

For example, there is a set of data:

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

The data to be output after the ranking should be:

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

4. Initializing Database Scripts

Create a database named Leetcode in the MySQL database and execute the following script with the source command in the MySQL command line:

--a databaseuse leetcode named Leetcode must be established before executing the script; DROP TABLE IF EXISTS Scores; CREATE TABLE Scores (Id INT not NULL PRIMARY KEY, score FLOAT); INSERT into Scores (IDs, score) VALUES (1, 3.50), insert into Scores (ID, score) VALUES (2, 3.65), insert into Scores (ID, Sc Ore) VALUES (3, 4.00), insert into Scores (ID, score) VALUES (4, 3.85), insert into Scores (ID, score) VALUES (5, 4.00); inse RT into Scores (Id, Score) VALUES (6, 3.65);

5, first of the following two other ranking methods

There are many ways to rank, and aside from this topic, there are two common ways to rank:

The first ranking method, is directly according to the score to the member arrangement, the position starts from 1 in turn increments, when two person's score is same, also must according to appear the order or other law divides successively. The SQL statements in this order are as follows:

Select Score, @Counter: = @Counter + 1 as Rankfrom Scores, (SELECT @Counter: = 0) as CORDER by score DESC;

The following are sorted:

(This SQL also applies to adding line numbers to a set of query results)

The second ranking method, is ranked by the number of members, if the two ID score is the same, then the same position, but does not affect the ranking by descendants of the rank. For example, a match two people tied first, then the game will no longer have a second, the second highest score will be counted as the third place. The SQL statements in this order are as follows:

SELECT S1. Score, (SELECT COUNT (score) + 1 from Scores S2 WHERE S1. Score < S2. Score) as Rankfrom Scores S1order by S1. Score DESC;

The following are sorted:

6. Solving SQL

The method I came up with was to use the DISTINCT keyword, the first place to drain the score (score) in a temporary result set, in the cross-origin table and this temporary result set match each ID corresponding to the rank. A SQL statement that implements this idea is:

Select A.score, B.rankfrom Scores as A, (select Score, @Counter: = @Counter + 1 as Rank from (select DISTINCT score FRO M Scores) as dis_s, (SELECT @Counter: = 0) as C ORDER by score desc) as Bwhere A.score = B.scoreorder by A.score DESC;

The query results are as follows:

7. Other problem Solving SQL

Later I looked at the following original topic of the Dicuss plate, there are many different SQL statements to implement this function, especially this post:

Https://leetcode.com/discuss/40116/simple-short-fast

Now put a few SQL and related ideas organized after the listed below

1) Use two variables rank and prev Store the current position and the score of the previous rank, the score changes, the rank plus one

Select Score, @rank: = @rank + (@prev <> (@prev: = score)) Rankfrom Scores, (SELECT @rank: = 0, @prev: =-1) Ini Torder by score DESC;

2) rank value for each ID, which is the number of data rows with a score less than that ID

Select Score, (select COUNT (DISTINCT score) from Scores WHERE score >= s.score) Rankfrom Scores SORDER by score D ESC;

3) Similar to the previous method

Select Score, (select COUNT (*) from (select DISTINCT score S from Scores) as Tmp WHERE S >= score) as Rank From Scoresorder by score DESC;

4) The following method, is also selected than the current ID score high score number, as the current ID corresponding to the rank

SELECT S.score, COUNT (DISTINCT t.score) as Rankfrom Scores s JOIN Scores T on S.score <= T.scoregroup by S.idorder by S . Score DESC

END

Leetcode:rank Scores-Rank by score