mysql | 计算排名

>　计算排名

例子：
https://www.1keydata.com/tw/sql/sql-rank.html

给出每个人的排名

SELECT a1.Name, a1.Sales, COUNT(a2.Sales) Sales_Rank FROM Total_Sales a1, Total_Sales a2 WHERE a1.Sales <= a2.Sales OR (a1.Sales=a2.Sales AND a1.Name = a2.Name) GROUP BY a1.Name, a1.Sales ORDER BY a1.Sales DESC, a1.Name DESC;

LeetCode上的计算排名：

https://leetcode.com/problems/rank-scores/description/

结果：

方案一：

SELECT  Score,  @rank := @rank + (@prev <> (@prev := Score)) RankFROM  Scores,  (SELECT @rank := 0, @prev := -1) initORDER BY Score desc

方案二：

SELECT  Score,  (SELECT count(distinct Score) FROM Scores WHERE Score >= s.Score) RankFROM Scores sORDER BY Score desc

方案三：

SELECT  Score,  (SELECT count(*) FROM (SELECT distinct Score s FROM Scores) tmp WHERE s >= Score) RankFROM ScoresORDER BY Score desc

方案四：

SELECT s.Score, count(distinct t.score) RankFROM Scores s JOIN Scores t ON s.Score <= t.scoreGROUP BY s.IdORDER BY s.Score desc

总结出来，以供自己学习之用。