MySQL:选择一个优胜者，返回他们的排名 [英] MySQL: SELECT a Winner, returning their rank

问题描述

我早些时候问过这个问题 ，基本上是根据他们的得分，询问如何在一个有很多获奖者的表中列出10个获奖者.

Earlier I asked this question, which basically asked how to list 10 winners in a table with many winners, according to their points.

已回答.

现在，我想在表格中搜索给定的获胜者X，并找出按点对表格进行排序的位置.

Now I'm looking to search for a given winner X in the table, and find out what position he is in, when the table is ordered by points.

例如，如果这是表:

     Winners:
NAME:____|__POINTS:
Winner1  |  1241
Winner2  |  1199
Sally    |  1000
Winner4  |  900
Winner5  |  889
Winner6  |  700
Winner7  |  667
Jacob    |  623
Winner9  |  622
Winner10 |  605
Winner11 |  600
Winner12 |  586
Thomas   |  455
Pamela   |  434
Winner15 |  411
Winner16 |  410

这些是我想做的可能的输入和输出:

These are possible inputs and outputs for what I want to do:

Query:  "Sally", "Winner12", "Pamela", "Jacob"
Output: 3        12          14        623

我该怎么做?是否可以仅使用MySQL语句?还是我也需要PHP?

How can I do this? Is it possible, using only a MySQL statement? Or do I need PHP as well?

这是我想要的东西

WHEREIS FROM Winners WHERE Name='Sally' LIMIT 1

想法?

编辑-注意:您不必处理两个优胜者拥有相同积分的情况(为简单起见，假设不会发生这种情况).

Edit - NOTE: You do not have to deal with the situation where two Winners have the same Points (assume for simplicity's sake that this does not happen).

推荐答案

我认为这将为您带来所需的结果.请注意，我会正确处理目标获胜者与另一获胜者并列积分的情况. (两者的位置相同).

I think this will get you the desired result. Note that i properly handles cases where the targeted winner is tied for points with another winner. (Both get the same postion).

SELECT COUNT(*) + 1 AS Position
FROM myTable
WHERE Points > (SELECT Points FROM myTable WHERE Winner = 'Sally')

修改:
我想插入" Ignacio Vazquez-Abrams 的答案，从几个方面来说，它比上面的要好.
例如，它允许列出所有(或几个)获奖者及其当前位置.
另一个优点是，它允许表达更复杂的条件，以指示给定玩家领先于另一个玩家(请参见下文).阅读 incrediman 的评论，即不会有联系"，这促使我对此进行了研究.可以对查询进行如下修改，以处理玩家拥有相同点数时的情况(这些玩家以前曾被赋予相同的位置值，现在位置值进一步与其相对的起始值联系在一起).

Edit:
I'd like to "plug" Ignacio Vazquez-Abrams' answer which, in several ways, is better than the above.
For example, it allows listing all (or several) winners and their current position.
Another advantage is that it allows expressing a more complicated condition to indicate that a given player is ahead of another (see below). Reading incrediman's comment to the effect that there will not be "ties" prompted me to look into this; the query can be slightly modified as follow to handle the situation when players have same number of points (such players would formerly have been given the same Position value, now the position value is further tied to their relative Start values).

SELECT w1.name, (
  SELECT COUNT(*)
  FROM winners AS w2
  WHERE (w2.points > w1.points) 
     OR (W2.points = W1.points AND W2.Start < W1.Start)  -- Extra cond. to avoid ties.
)+1 AS rank
FROM winners AS w1
-- WHERE W1.name = 'Sally'   -- optional where clause

这篇关于MySQL:选择一个优胜者，返回他们的排名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！