php - 使用特定标准查找最大值并检查同一组是否有更高/更低或任何值

Question

我正在尝试获取游戏...并且用户和信息的最高分值(其他玩家的分数)高于/低于用户的最高分值。我会解释情况。

用户应该获得他的游戏和每个游戏的最高分数，其中游戏是事件的。这适用于 JOIN 和 GROUP BY 但我不知道如何获取信息是否有更多分数以及它们是否高于或低于用户最高分数。

表 1:分数

+----+--------+------+--------+-------+
| id | gameID | game | player | score |
+----+--------+------+--------+-------+
|  1 |      1 | pang | lea    |    50 |
|  2 |      1 | pang | lea    |    60 |
|  3 |      1 | pang | paola  |    70 |
|  4 |      2 | pong | lea    |   100 |
|  5 |      2 | pong | paola  |    90 |
+----+--------+------+--------+-------+

表 2:游戏

+----+------+--------+
| id | name | active |
+----+------+--------+
|  1 | pang | yes    |
|  2 | pong | yes    |
|  3 | pung | yes    |
+----+------+--------+

代码:

$loggedUser = 'lea';
$sql = 
    "
SELECT s.gameID
     , s.game
     , COUNT(s.id) c
     , MAX(s.score) Max
  FROM scores s
 RIGHT 
  JOIN games g
    ON s.gameID = g.id
 WHERE g.active = 'yes' 
   AND s.player = '$loggedUser'
 GROUP 
    BY s.gameID 
 ORDER 
    BY c
";

输出应该是这样的:

Hello Lea, your top scores:

pang played x 2, max score 60 pts (top score is 70)....

pong played x 1, 100 pts , you have higher score....

Answer 1

在 MySQL 8.0 中，您可以使用窗口函数来:

• 对每场比赛的球员得分进行排名:ROW_NUMBER() OVER(PARTITION BY player, game ORDER BY score DESC)
• 计算玩家玩每场游戏的次数:COUNT(*) OVER(PARTITION BY player, game)
• 计算给定游戏在所有玩家中的最高分:MAX(score) OVER(PARTITION BY game ORDER BY score DESC)

考虑以下查询:

SELECT game_name, cnt games_played, score max_score, top_score
FROM (
    SELECT
        g.name game_name,
        s.player,
        s.score,
        ROW_NUMBER() OVER(PARTITION BY player, game ORDER BY score DESC) rn,
        COUNT(*) OVER(PARTITION BY player, game) cnt,
        MAX(score) OVER(PARTITION BY game ORDER BY score DESC) top_score
    FROM scores s
    INNER JOIN games g ON g.id = s.gameID AND g.active = 'yes'
) x WHERE rn = 1 AND player = 'lea';

在 this db fiddle 使用您的样本数据，查询产生:

| game_name | games_played | max_score | top_score |
| --------- | ------------ | --------- | --------- |
| pang      | 2            | 60        | 70        |
| pong      | 1            | 100       | 100       |

---

在早期版本的 MySQL 中，一种解决方案是使用聚合子查询来计算所有玩家中每场比赛的最高分，并将其与主聚合查询JOIN:

SELECT g.name game_name, COUNT(*) games_played, MAX(s.score) max_score, ts.top_score
FROM  scores s
INNER JOIN games g 
    ON g.id = s.gameID AND g.active = 'yes'
INNER JOIN (SELECT gameID, MAX(score) top_score FROM scores GROUP BY gameID) ts 
    ON s.gameID = ts.gameID
WHERE s.player = 'lea'
GROUP BY s.player, g.id, g.name;

Demo on DB Fiddle .