MySQL查询帮助按ID查找排名

| 我正在尝试修改以下查询以查找特定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子句中，总是将排名显示为＃1位置，因为它仅返回一行：

  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，但仍保留排名？仅供参考，如果有帮助，我会保留两列（输赢）。

已邀请:

3 个回复

览幕堤分

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

门锑腺潮

尝试这样的事情：

SELECT videoid, 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) s WHERE videoid = 116

诫商

我已经在您描述的类似表创建的简单子集上对此进行了测试... 它返回一个视频及其整个集合的实际最终排名。

select *
   from ( SELECT 
              videoid, 
              wins, 
              losses, 
              wins/losses,       
              @curRank := @curRank +1 Rank
           FROM 
              cb_video,
              ( select @curRank := 0 ) r
           order by 
              wins/losses desc ) PreQuery
    where
       PreQuery.VideoID = 116

要回复问题请先登录或注册

MySQL查询帮助按ID查找排名

3 个回复

发起人

mysql

sql

ranking

问题状态

MySQL查询帮助按ID查找排名

与内容相关的链接

3 个回复

发起人

mysql

sql

ranking

问题状态