获取最大值和对应的列

小编典典

获取最大值和对应的列

sql

如何从mysql中的max查询中获取相应的列？我想知道一个球员有多少个胜利。通过计算玩家赢得的游戏数量，我会发现这一点。可以通过选择每个游戏的最大值和最终的player_id来完成。但是我不确定如何获取相应的player_id。

我有

   id   |   game_id | player_id | score

    1   |     1     |     1     |   254
    2   |     1     |     2     |   194
    3   |     2     |     1     |   432
    4   |     2     |     2     |   298

阅读 241

2021-03-17

共1个答案

小编典典

这个查询应该得到您所需要的：

SELECT
    player_id, game_id, score
FROM
(
    SELECT game_id,MAX(score) AS MaxScore
    FROM games
    GROUP BY game_id
) AS Winners
JOIN games
    ON (games.game_id = Winners.game_id AND games.score = Winners.MaxScore)

假设平局对双方都是胜利。

SQLFiddle

如果您只想获得球员及其获胜次数，则可以使用以下查询：

SELECT
    player_id, COUNT(*) AS wins
FROM
(
    SELECT game_id,MAX(score) AS MaxScore
    FROM games
    GROUP BY game_id
) AS Winners
JOIN games
    ON (games.game_id = Winners.game_id AND games.score = Winners.MaxScore)
WHERE player_id = {player_id}
GROUP BY player_id

只需替换{player_id}为您要寻找的球员wins，即可获胜或平局的数量。

2021-03-17