MySQL之分数排名
问题：
编写一个 SQL 查询来实现分数排名。如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。
| 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 |
答案：
MySQL使用自连接
SELECT
a.score AS Score,
COUNT(DISTINCT b.score) AS Rank
FROM
scores a,
scores b
WHERE b.score >= a.score
GROUP BY a.id
ORDER BY a.score DESC
select s.Score,(select count(distinct Score)
from Scores where Score>=s.Score)
as Rank from Scores s
order by s.Score desc
我的：
SELECT
s1.Score,
s2.Rank
FROM
Scores s1
LEFT JOIN (
SELECT
( @i := @i + 1 ) AS Rank,
Score
FROM
( SELECT * FROM Scores GROUP BY Score ORDER BY Score DESC ) s,
( SELECT @i := 0 ) it
) s2 ON s1.Score = s2.Score
ORDER BY
s2.Rank
