LeetCode MySQL-178. Rank Scores

    AllenLi
    LeetCode MySQL

    Write an SQL query to rank the scores. The ranking should be calculated according to the following rules:
    The scores should be ranked from the highest to the lowest.
    If there is a tie between two scores, both should have the same ranking.
    After a tie, the next ranking number should be the next consecutive integer value. In other words, there should be no holes between ranks.
    Return the result table ordered by score in descending order.

    寫一個 SQL Query來排行分數。 排行榜應該依據以下規則做計算：
1. 分數必須由大到小。
2. 如果是兩個相同的分數，則該在同一個排名。
3. 在平局之後，下一個排名應當是下一個連續的整數值。(排名皆不會有跳號的情況)
返回分數降冪排序的結果。

    Table: Scores

    +-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| score       | decimal |
+-------------+---------+
id is the primary key for this table.
Each row of this table contains the score of a game. Score is a floating point value with two decimal places.

    Example 1:

    Input: 
Scores table:
+----+-------+
| id | score |
+----+-------+
| 1  | 3.50  |
| 2  | 3.65  |
| 3  | 4.00  |
| 4  | 3.85  |
| 5  | 4.00  |
| 6  | 3.65  |
+----+-------+
Output: 
+-------+------+
| score | rank |
+-------+------+
| 4.00  | 1    |
| 4.00  | 1    |
| 3.85  | 2    |
| 3.65  | 3    |
| 3.65  | 3    |
| 3.50  | 4    |
+-------+------+

    Solution:
    1. 選擇標題 t1.score。
    2. 選擇標題 rank ，為 Scores 中建立 t2 的表單，且選取兩者表單的分數，如有相同則計算不重複的次數。

    example:

score1 = [5, 6, 6, 8]
score2 = [5, 6, 6, 8]

COUNT(DISTINCT(score2 >= score1))
       = [3, 2, 2, 1]
---------------------------------
分別為  :  8  8  8  8
          6  6  6
          5

    3. 來自 Scores 中命名為 t1 的表單
    4. 依照 t1.score 降冪排序。

    Code.1:

    SELECT t1.score,
    (SELECT COUNT(DISTINCT(t2.score))
        FROM Scores AS t2
        WHERE t2.score >= t1.score) AS `rank`
FROM Scores AS t1
ORDER BY t1.score DESC;

    Code.2:

    SELECT score, 
	DENSE_RANK() OVER(ORDER BY score DESC) AS `rank`
FROM Scores;

    newRankScores

    DECIMAL

    DECIMAL: DECIMAL(有效位數, 小數位數)

    DENSE_RANK()

    DENSE_RANK ( ) OVER ( [  ] < order_by_clause > )

    Related Posts