时间戳

首页 > 解决方案 > MySQL 限制用于计算 SUM 的行数

mysql - MySQL 限制用于计算 SUM 的行数

问题描述

我正在制作一个游戏，每小时计算玩家的分数。需要分数，以便排行榜可以显示排名最高的玩家。游戏中赢得的“积分”存储在boards_played 表中。玩家表有一个 score 字段，每小时使用以下 SQL 更新：

update players p 
       inner join (
          SELECT player_id, sum(points) as total 
            from boards_played
           where time_played > DATE_SUB(now(), INTERVAL 7 DAY)
           GROUP BY player_id) s on p.player_id = s.player_id
  set p.score = s.total

问题是从 100 场比赛中总共赢得 50 分的人应该比从 200 场比赛中赢得 60 分的人排名更好。

简而言之，分数应仅基于您在过去 7 天内的最后 100 场比赛。

标签： mysqlinner-joinlimit

解决方案

您可能在这里需要一个相关的子查询，这样您就可以为每个玩家检索正确的值。

要查找某个玩家的总分，这个子查询会执行此操作。

          SELECT SUM(points) as total
            FROM (
                   SELECT player_id, points
                     FROM boards_played
                    WHERE time_played > DATE_SUB(NOW(), INTERVAL 7 DAY)
                     AND player_id = ***something*** 
                    ORDER BY time_played DESC
                    LIMIT 100
                 ) a

现在，您需要将其合并到您的外部查询中

UPDATE players p
   SET score =
       (
          SELECT SUM(points) as total
            FROM (
                   SELECT points
                     FROM boards_played
                    WHERE time_played > DATE_SUB(NOW(), INTERVAL 7 DAY)
                      AND points.player_id = p.player_id 
                    ORDER BY time_played DESC
                    LIMIT 100
                 ) a
       )

为什么这被称为相关？points.player_id = p.player_id子查询中的行将其与外部查询相关联。这样做可以LIMIT将 分别应用于每个玩家的分数。

但是，您最好制作一个可以即时计算此值的视图，而不是更新您的表。这样您就不必一直担心更新您的表格。它看起来像这样（未调试）。

CREATE VIEW players_with_score AS
SELECT p.player_id, p.col1, p.col2, p.col3,
       (  
          SELECT SUM(points) as total
            FROM (
                   SELECT points
                     FROM boards_played
                    WHERE time_played > DATE_SUB(NOW(), INTERVAL 7 DAY)
                      AND points.player_id = p.player_id 
                    ORDER BY time_played DESC
                    LIMIT 100
                 ) a
       ) score
  FROM player p

然后你可以说像

  SELECT player_id, score
    FROM players_with_score
   WHERE score > 250

推荐阅读