显示 MySQL 表中的单个排名

发布于 2024-08-20 00:50:37 字数 366 浏览 7 评论 0原文

我有一个名为“highscores”的表，如下所示。

id      udid       name       score
1       1111       Mike       200
2       3333       Joe        300
3       4444       Billy      50
4       0000       Loser      10
5       DDDD       Face       400

给定一个特定的 udid，我想按分数值返回该行的排名。

即如果给定的 udid = 0000，我应该返回 5。

知道如何为 MySQL 数据库编写此查询吗？

原文

I have a table called 'highscores' that looks like this.

id      udid       name       score
1       1111       Mike       200
2       3333       Joe        300
3       4444       Billy      50
4       0000       Loser      10
5       DDDD       Face       400

Given a specific udid, I want to return the rank of that row by their score value.

i.e. if udid given = 0000, I should return 5.

Any idea how to write this query for a MySQL database?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

无语# 2024-08-27 00:50:37

MySQL 没有任何分析/排名功能，但您可以使用变量人为创建排名值：

  SELECT t.id,
         t.udid,
         t.name,
         t.score,
         @rownum := @rownum + 1 AS rank
    FROM HIGHSCORES t
    JOIN (SELECT @rownum := 0) r
ORDER BY t.score DESC

为了查看与 UDID“0000”关联的排名，请使用：

SELECT MAX(x.rank) AS rank
  FROM (SELECT t.id,
               t.udid,
               t.name,
               t.score,
               @rownum := @rownum + 1 AS rank
          FROM HIGHSCORES t
          JOIN (SELECT @rownum := 0) r
      ORDER BY t.score DESC) x
 WHERE x.udid = '0000'

需要 < code>MAX 表示用户是否有多个高分值。或者，您不能使用 MAX 并使用 ORDER BYrank LIMIT 1。

MySQL doesn't have any analytic/ranking functionality, but you can use a variable to artificially create a rank value:

  SELECT t.id,
         t.udid,
         t.name,
         t.score,
         @rownum := @rownum + 1 AS rank
    FROM HIGHSCORES t
    JOIN (SELECT @rownum := 0) r
ORDER BY t.score DESC

In order to see what rank is associated with UDID "0000", use:

SELECT MAX(x.rank) AS rank
  FROM (SELECT t.id,
               t.udid,
               t.name,
               t.score,
               @rownum := @rownum + 1 AS rank
          FROM HIGHSCORES t
          JOIN (SELECT @rownum := 0) r
      ORDER BY t.score DESC) x
 WHERE x.udid = '0000'

Need the MAX for if the user has multiple high score values. Alternately, you could not use MAX and use ORDER BY rank LIMIT 1.

回复收藏 0 原文

表情可笑 2024-08-27 00:50:37

为了重申 OMG 的出色答案，即每个 udid 具有多个高分的一般情况，以下是基于每个 udid 恰好有一个条目的前提条件的查询：

SELECT rank
FROM
   (SELECT @rownum := @rownum + 1 AS rank, score, udid
    FROM highscores
    JOIN (SELECT @rownum := 0) r
    ORDER BY highscores.score DESC) x
WHERE x.udid = '0000'

To reiterate OMG's excellent answer which is the general case of multiple high scores per udid, here's the query based on the precondition of exactly one entry per udid:

SELECT rank
FROM
   (SELECT @rownum := @rownum + 1 AS rank, score, udid
    FROM highscores
    JOIN (SELECT @rownum := 0) r
    ORDER BY highscores.score DESC) x
WHERE x.udid = '0000'

回复收藏 0 原文

~没有更多了~