基于两列的排名 [英] Rank based on two columns

问题描述

我需要有关mysql查询的帮助。该查询必须能够显示排名，如图片 http://s27.postimg.org /loixkzipv/img.png 。

I need help with a mysql query. The query has to be able to show rank, as in the picture http://s27.postimg.org/loixkzipv/img.png .

如果获胜相同，则与 diff列进行比较，以保持列表顺序。请帮助此查询。谢谢。

If the wins are same, compare against the "diff" column, maintaining list order. Please help with this query. Thank you.

我已经尝试过：

 SELECT player, wins, diff, rank 
 FROM (
     SELECT player, wins, diff, 
         @curRank := IF(@prevRank = diff, @curRank, @incRank) AS rank, 
         @incRank := @incRank + 1, 
         @prevRank := diff 
     FROM tmpPoradi p, (SELECT @curRank := 0, @prevRank := NULL, @incRank := 1) r  
     ORDER BY diff 
     DESC) s;

但是此函数仅比较diff，不引用获胜。

But this function only compares diff, and doesn't reference wins. How do I make it take into account wins as well?

推荐答案

我认为以下查询将获得您所需的结果。首先，您必须在子查询中计算排名。我认为这比您的方法要简单得多，您只需要按获胜和差异按降序对结果进行排序（假设字段是数字），然后只需选择所有结果并进行更改即可

I think that the following query will get the result you need. First you have to calculate rank in a subquery.I think is much simpler than your approach, you just have to sort your results by wins and diff in descending order (supposing the fields are numbers).Then you just select all the results and change order to player desc.

 SELECT player, wins, diff,rank from
 (
 SELECT player, wins, diff, @winrank := @winrank + 1 AS rank
 from tmpPoradi,(SELECT @winrank := 0) r 
 ORDER BY wins DESC,diff DESC
 )  rt
ORDER BY player

希望我没有丢失任何东西。

Hope I am not missing anything.

这篇关于基于两列的排名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！