mysql - 与另一个表连接后对 MySQL 结果进行排名，也按条件过滤它们

Question

我找到了如何对结果进行排名，但还没有找到在按条件过滤后如何对结果进行排名的解决方案。我需要做的是:

1) 分数降序排列
2) 将结果与account表join
3) 按年龄和性别过滤结果(来自账户表)
4) 给他们排名
5) 抓取一个ranks范围内的结果(即:25-75，但为了简单起见，我在下面放了0-10)

我发现的每个解决方案都没有正确地对它们进行排名，或者对它们进行排名然后过滤结果。但是，当我想要 1、2、3、4、5 等或 25、26、27、28、29 等时，这给我留下了 1、3、4、6、10 等等级。

这是我现在拥有的:

SELECT a.id, a.username, a.country, score, user_id, duration, rank
FROM (
    SELECT s.*, a.age, a.gender, @rownum := @rownum + 1 AS rank
    FROM scores s,
        (SELECT @rownum := 0) r
    LEFT JOIN accounts a ON 1
    WHERE a.age>=18 && a.age<=35 && a.gender='m'
    ORDER BY score DESC, duration DESC, time ) `selection`
LEFT JOIN accounts a ON a.id=user_id
WHERE rank >= 0 && rank <= 10 && a.age>=18 && a.age<=35 && a.gender='m'

上述查询的问题是它给我正确的排名，没有任何差距，但分数完全乱序。 IE。 5、1、10、7、125、50 等。IE。它们的输出顺序与它们在数据库中的顺序相同，未排序。

这是我之前的查询的样子:

SELECT a.id, a.username, a.country, score, duration, rank
FROM (
    SELECT s.*, @rownum := @rownum + 1 AS rank
    FROM scores s,
        (SELECT @rownum := 0) r
    ORDER BY score DESC, duration DESC, time ) `selection`
LEFT JOIN accounts a ON a.id=user_id
WHERE rank >= 0 && rank <= 10 && a.age>=18 && a.age<=35 && a.gender='m'

此查询为我提供了正确排序的分数，但排名为 3、5、8、9、11，而不是 1、2、3、4、5。

这是一些真实世界的示例数据...
为了节省时间，id、用户名、国家/地区都被省略了，因为它们是不相关的数据，只是在表格中一直重复。正如条件显示的那样，一直向下的年龄是“34”，国家和用户名一直向下都是相同的，因为这个特定用户是表中唯一一个 34 岁的用户。

上面的第一个查询具有正确的排名，但分数排序不正确(即，与添加到数据库中的顺序相同):

  age | score | rank
--------------------
  34  |   5   |  1
  34  |   1   |  2
  34  |  22   |  3
  34  |  13   |  4
  34  |  23   |  5
  34  |  23   |  6
  34  |  34   |  7
  34  |  32   |  8
  34  |  58   |  9
  34  |  76   |  10

上面的第二个查询，正确排序，但排名时仍然考虑其他用户，然后在最终结果中被排除:

  age | score | rank
---------------------
  34  |  76   |  3  --- This should have started at 1
  34  |  62   |  4
  34  |  58   |  5
  34  |  42   |  7  --- Notice 6 was skipped
  34  |  34   |  8
  34  |  32   |  9
  34  |  29   |  10

我的期望:

  age | score | rank
--------------------
  34  |  76   |  1
  34  |  62   |  2
  34  |  58   |  3
  34  |  42   |  4
  34  |  34   |  5
  34  |  32   |  6
  34  |  29   |  7

Answer 1

我可以根据您的示例数据集提供以下查询:

SET @rank = 0;
SET @score = NULL;

SELECT
    age,
    @rank:=CASE WHEN @score = score THEN @rank ELSE @rank + 1 END AS rank,
    @score:=score AS score
FROM scores
ORDER BY rank;

根据通常使用“排名”的定义计算分数的排名。如果两个分数相同，则它们具有相同的排名。如果你真的想要行号，那么上面的查询可以很容易地修改。

输出:

此处演示:

Rextester