SQL implementation of LeetCode (178. Score ranking)

[LeetCode] 178.Rank Scores

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.

+----+-------+
| 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 |
+-------+------+

This question gives us a score table and asks us to sort the scores. The requirement is that the same scores are in the same rank, and the next score is in the next consecutive rank, and there cannot be any blank numbers in between. I wrote this question completely according to the post of Stephen. I worship the great god... The great god summarized four methods, so let's worship and learn one by one. First, look at the first solution. The idea of ​​solving the problem is to find out how many different scores in the table are greater than or equal to each score, and then arrange them in descending order. See the code as follows:

Solution 1:

SELECT Score, 
(SELECT COUNT(DISTINCT Score) FROM Scores WHERE Score >= s.Score) Rank 
FROM Scores s ORDER BY Score DESC;

The solution is the same as above, but the writing method is slightly different:

Solution 2:

SELECT Score,
(SELECT COUNT(*) FROM (SELECT DISTINCT Score s FROM Scores) t WHERE s >= Score) Rank
FROM Scores ORDER BY Score DESC;

The following solution uses inner join. Join is the abbreviation of Inner Join. It inner joins itself with itself. The condition is that the score of the right table is greater than or equal to the left table. Then the groups are grouped and arranged in descending order of the scores. It is a very clever solution:

Solution 3:

SELECT s.Score, COUNT(DISTINCT t.Score) Rank
FROM Scores s JOIN Scores t ON s.Score <= t.Score
GROUP BY s.Id ORDER BY s.Score DESC;

The following solution is different from the above three solutions. Two variables are used here. @ needs to be added in front of the variable when used. The := here means assignment. If there is a Set keyword in front, you can directly use the = sign to assign a value. If not, you must use := to assign a value. There are two variables rank and pre, where rank represents the current ranking and pre represents the previous score. The <> in the following code means not equal. If the left and right sides are not equal, true or 1 is returned. If they are equal, false or 0 is returned. Initialize rank to 0 and pre to -1, and then arrange the scores in descending order. For score 4, pre is assigned to 4, which is different from the previous pre value of -1, so the rank is increased by 1, so the rank of score 4 is 1. The next score is still 4, so pre is assigned to 4, which is the same as the previous 4, so the rank is increased by 0, so the rank of this score 4 is also 1, and so on, the rank of all scores can be calculated.

Solution 4:

SELECT Score,
@rank := @rank + (@pre <> (@pre := Score)) Rank
FROM Scores, (SELECT @rank := 0, @pre := -1) INIT 
ORDER BY Score DESC;

References:

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

This is the end of this article about SQL implementation of LeetCode (178. Score ranking). For more relevant SQL implementation score ranking content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!