MySQL 查询帮助按 ID 查找排名

Question

我正在尝试修改以下查询以查找特定 videoid 的排名，但我运气不佳，有人可以提出解决方案吗？

  SELECT videoid wins/loses as win_loss, 
         @curRank := @curRank + 1 AS rank
    FROM cb_video, 
         (SELECT @curRank := 0) r  
ORDER BY wins/loses DESC

我尝试过这样的子查询，但失败了:

SELECT rank 
  FROM (SELECT videoid wins/loses as win_loss, 
               @curRank := @curRank + 1 AS rank 
          FROM cb_video, 
               (SELECT @curRank := 0) r  
      ORDER BY wins/loses DESC) 
 WHERE videoid = 116

在没有子查询的情况下将 videoid 添加到 WHERE 子句中只会始终显示排名第一的位置，因为它只返回一行:

  SELECT videoid wins/loses as win_loss, 
         @curRank := @curRank + 1 AS rank 
    FROM cb_video, 
         (SELECT @curRank := 0) r 
   WHERE videoid = 116 
ORDER BY wins/loses DESC

关于如何将结果限制为特定 ID 但仍保留排名的任何想法？仅供引用，如果有帮助，我会保留两列(赢和输)。

Answer 1

SELECT a.videoid, 
(SELECT COUNT(*) FROM cb_video b 
 WHERE a.videoid !=b.videoid 
 AND (b.wins/b.loses) > (a.wins/a.loses))+1 AS rank
FROM cb_video a
WHERE a.videoid = 116