题目
代码部分一(1537ms)
SELECT
Score,(SELECT COUNT(DISTINCT Score) FROM Scores s2 WHERE s2.Score>=s1.Score) AS Rank
FROM Scores s1
ORDER BY Rank
代码部分二(910ms)
SELECT
s1.Score,COUNT(DISTINCT s2.Score) AS Rank
FROM Scores s1
JOIN Scores s2 ON s2.Score>=s1.Score
GROUP BY s1.Id
ORDER BY s1.Score DESC;
代码部分三(890ms)
SELECT
Score,(SELECT COUNT(DISTINCT Score) FROM Scores s2 WHERE s2.Score>=s1.Score) AS Rank
FROM Scores s1
ORDER BY Score DESC
代码部分四(419ms)
SELECT
s.Score,Count(Ranking.Score) AS Rank
FROM Scores s,(
SELECT DISTINCT Score
FROM Scores
) AS Ranking
WHERE Ranking.Score>=s.Score
GROUP BY s.id
ORDER BY s.Score DESC
代码部分五(251ms)
SELECT Scores.Score, COUNT(Ranking.Score) AS RANK
FROM Scores
, (
SELECT DISTINCT Score
FROM Scores
) Ranking
WHERE Scores.Score <= Ranking.Score
GROUP BY Scores.Id, Scores.Score
ORDER BY Scores.Score DESC;
代码部分六(160ms)
SELECT Score, CONVERT ( CASE
WHEN @preValue = Score THEN @preRank
WHEN (@preValue := Score) >= 0 THEN @preRank := @preRank + 1
END, UNSIGNED) AS Rank
FROM Scores, (SELECT @preValue := NULL) p, (SELECT @preRank := 0) v
ORDER BY Score DESC;