难度:中等
写一段SQL语句查询分数排名情况。如果两个分数相等,则排名相同。
+----+-------+ | Id | Score | +----+-------+ | 1 | 3.50 | | 2 | 3.65 | | 3 | 4.00 | | 4 | 3.85 | | 5 | 4.00 | | 6 | 3.65 | +----+-------+
例如:按上表 Scores
的数据, 你的查询语句返回下列数据(从大到小排序):
+-------+------+ | Score | Rank | +-------+------+ | 4.00 | 1 | | 4.00 | 1 | | 3.85 | 2 | | 3.65 | 3 | | 3.65 | 3 | | 3.50 | 4 | +-------+------+
答案:
select a.Score, (select count(distinct Score) from Scores where Score>=a.Score) as Rank from Scores a order by a.Score desc