在Mysql中，定义的变量来计算等级，案例语句检查竞争是否与上一行相同，然后如果不同则递增等级，然后分配1个顺序，以获得视频的正确排名

  SELECT t。*，
 @current_rank：= CASE WHEN @current_rank = competition 
 THEN @video_rank：= @ video_rank +1 
 ELSE @video_rank：= 1 END video_rank，
 @current_rank：= competition 
 FROM t，
（SELECT @video_rank：= 0，@ current_rank：= 0）r 
 ORDER BY比赛desc，vote desc 
 

请参阅演示

如果您对最后一个额外的列感到困惑，可以使用子选项

请参阅演示

I am trying to figure out how to rank based on 2 different column numbers in laravel but raw mysql will do. I have a list of videos, these videos are inside of competitions and are given votes if someone likes the video. Each video will have a vote number and a competition number. I am trying to rank based on votes within competition. So for example below I have competition 8, I need the rank of all the videos in that competition based on votes. I then need the same for competition 5 etc.

  |rank|votes|competition|
  ------------------
  |  1 | 100 | 8   |
  ------------------
  |  2 |  50 | 8   |
  ------------------
  |  3 |  30 | 5   |
  ------------------
  |  1 | 900 | 5   |
  ------------------
  |  2 |  35 | 5   |
  ------------------

I have tried various group and selectby methods but nothing seems to work, any ideas?

In Mysql you can use user-defined variables to calculate rank,case statement checks if competition is same as the previous row then increment rank by one if different then assign 1 an order by is needed to have correct rank for the video

 SELECT t.*,
   @current_rank:= CASE WHEN @current_rank = competition 
             THEN  @video_rank:=@video_rank +1 
             ELSE @video_rank:=1 END  video_rank,
   @current_rank:=competition
FROM t ,
(SELECT @video_rank:=0,@current_rank:=0) r   
  ORDER BY competition desc, votes desc

See Demo

If you are confused with the last extra column you can use a subselect

See Demo