心得
題目要求找出成績排名,如果分數相同的話則相同名次,MySQL不像MSSQL有ROW_NUMBER()
可以用,只好用個變數來存了。
問題
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.
12345678910 +----+-------+| 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):
12345678910 +-------+------+| Score | Rank |+-------+------+| 4.00 | 1 || 4.00 | 1 || 3.85 | 2 || 3.65 | 3 || 3.65 | 3 || 3.50 | 4 |+-------+------+
答案
1 2 3 4 5 6 7 8 9 10 11 |
# Write your MySQL query statement below SELECT a.Score, b.Rank FROM Scores AS a JOIN (SELECT (@row_number:=@row_number + 1) AS Rank, z.Score FROM (SELECT x.* FROM Scores AS x GROUP BY x.Score ORDER BY x.Score DESC) AS z JOIN (SELECT @row_number := 0) AS y) AS b ON a.Score = b.Score ORDER BY `b`.`Score` DESC |