【leetcode Database】178. Rank Scores

来源：互联网发布：搬瓦工 python vps 编辑：程序博客网时间：2024/05/17 06:45

题目：

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

解析：先对Scores表排序，然后声明一个rank字段，当score相等时，rank不变，score不等时，rank+1。代码如下：

 SELECT Score,Rank FROM  (SELECT Score, @Rank := IF(@prevScore = Score,@Rank,@Rank+1) AS Rank, @prevScore := Score FROM Scores ORDER BY Score DESC) s,  (SELECT @Rank := 0, @prevScore := NULL) r;

0 0