MySQL 获取日期之间的玩家排名

发布于 2024-11-19 11:03:04 字数 585 浏览 2 评论 0原文

我有一个玩家表：

id  points  last_online
 1     320  2011-07-10
 2    1025  2011-07-05
 3     750  2011-04-25
 4    5000  2011-07-10
 5     525  2011-05-01

为了获得玩家排名（基于积分），我有以下 mySQL 选择：

SELECT Player.*,
    ( SELECT COUNT(*)
      FROM players Player_i
      WHERE (Player_i.points, Player_i.id) >= (Player.points, Player.id)
     ) AS rank
FROM players AS Player
WHERE Player.id = 1

它工作正常。因此，玩家 ID 1 的排名是 5。

但我只想考虑过去 30 天内上次在线的玩家（考虑到今天是 2011-07-10）。这样，玩家 ID 1 的排名将为 3，因为玩家 3 和 5 被排除在排名之外。

知道我该怎么做吗？

原文

I have a table of players:

id  points  last_online
 1     320  2011-07-10
 2    1025  2011-07-05
 3     750  2011-04-25
 4    5000  2011-07-10
 5     525  2011-05-01

In order to get a player rank (based on points), I have the following mySQL select:

SELECT Player.*,
    ( SELECT COUNT(*)
      FROM players Player_i
      WHERE (Player_i.points, Player_i.id) >= (Player.points, Player.id)
     ) AS rank
FROM players AS Player
WHERE Player.id = 1

It works fine. So, the rank for the player ID 1 is 5.

But I want to consider only the players that were online last time in the past 30 days (considering today is 2011-07-10). This way the rank for the player ID 1 would be 3, because the players 3 and 5 were excluded from the rank.

Any idea how I can do this?

分享到QQ

分享到微博

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

发布评论

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

旧瑾黎汐 2024-11-26 11:03:04

两种方式。第一种方法，稍微编辑一下查询：

SELECT Player.*,
    ( SELECT COUNT(*)
      FROM players Player_i
      WHERE Player_i.points >= Player.points
      and last_online > subdate(now(), '30 day')
     ) AS rank
FROM players AS Player
WHERE Player.id = 1
and last_online > subdate(now(), '30 day'); -- EDITED to add this line

第二种方法，也是更好的方法，是使用变量（更好的性能和更简单的 SQL）：

set @rank := 0;
SELECT *, @rank := @rank + 1
FROM Player
WHERE last_online > subdate(now(), 30 'day')
ORDER BY points desc;

Two ways. First way, edit your query a little:

SELECT Player.*,
    ( SELECT COUNT(*)
      FROM players Player_i
      WHERE Player_i.points >= Player.points
      and last_online > subdate(now(), '30 day')
     ) AS rank
FROM players AS Player
WHERE Player.id = 1
and last_online > subdate(now(), '30 day'); -- EDITED to add this line

Second way, and the better way, is to use a variable (way better performance, and simpler SQL):

set @rank := 0;
SELECT *, @rank := @rank + 1
FROM Player
WHERE last_online > subdate(now(), 30 'day')
ORDER BY points desc;

回复收藏 0 原文

洋洋洒洒 2024-11-26 11:03:04

添加 WHERE last_online > NOW() - '30 天' 到您的查询：

SELECT Player.*,
    ( SELECT COUNT(*)
    FROM players Player_i
    WHERE (Player_i.points, Player_i.id) >= (Player.points, Player.id)
        AND Player.last_online >= SUBDATE(NOW(), '30 day')
    ) AS rank
FROM players AS Player
WHERE Player.id = 1
    AND Player.last_online >= SUBDATE(NOW(), '30 day');

Add a WHERE last_online > NOW() - '30 days' to your query:

SELECT Player.*,
    ( SELECT COUNT(*)
    FROM players Player_i
    WHERE (Player_i.points, Player_i.id) >= (Player.points, Player.id)
        AND Player.last_online >= SUBDATE(NOW(), '30 day')
    ) AS rank
FROM players AS Player
WHERE Player.id = 1
    AND Player.last_online >= SUBDATE(NOW(), '30 day');

回复收藏 0 原文

~没有更多了~