【MySQL】用MYSQL排名

1. 分數相同名次相同



SELECT a.rank AS 'Rank', ID,SCORE FROM (SELECT ID,SCORE, @prev := @curr, @curr := SCORE,  @rank := IF(@prev = @curr, @rank, @rank + 1) AS rank  FROM user_data, (SELECT @curr := null, @prev := null, @rank := 0) s  where THEDATE ='2018-05-10' ORDER BY SCORE DESC limit 10) a;


輸出結果：









2. 分數相同名次不同



SELECT  @rownum := @rownum + 1 AS 'rownum',SCORE FROM  user_data,(SELECT @rownum := 0) b where SCHEDULE_DATE ='2018-05-10' ORDER BY SCORE DESC limit 10;


輸出結果：




3. 運用COUNT出的值排序，此種排序的結果即使值一樣但名次還是不同

select @rownum := @rownum +1 as rank,a.vote from ( select @rownum := 0 ) sqlvars,( SELECT  count(numbers) vote from user_data group by numbers order by count(numbers) desc ) a;