获取最大值和对应的列 [英] Get Max value and corresponding column

问题描述

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

How can I get corresponding columns from a max query in mysql? I want find out how many wins a player has. I will find that out by doing a count of the number of games that player has won. The will be done by selecting the max value per game and resulting player_id. However I am not sure how to get the corresponding player_id.

我有

   id   |   game_id | player_id | score

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

推荐答案

此查询应能满足您的需求:

This query should get what you need:

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)

它假设平局对双方都是胜利.

It assumes that a tie is a win for both players.

SQLFiddle

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

If you want to get just the player and their number of wins, you can use this query:

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是他们的获胜或平局数.

Just replace {player_id} with the player you're looking for and wins is their number of wins or ties.

这篇关于获取最大值和对应的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！