MySQL使用最大值选择细节 [英] MySQL Selecting details using max

问题描述

我有一套包含足球管理游戏信息的MySQL表。

这些表是：

• 玩家 - playerID（PK），playerName


• 匹配 - matchID（PK），matchSeason，matchRound，matchType


• PlayersMatch - playerID，matchID（comp PK），matchRating，playerForm，playerAge，position（可以为null）
  

表格与玩家的表现有关。玩家在比赛中进行比赛并且具有额定表现（matchRating）。 PlayersMatch中有一个参赛者参与的每场比赛的记录，记录了球员目前的表格，比赛成绩，比赛时的年龄（用于历史目的）以及他们的位置。

现在，我使用以下查询来列出整个赛季前10名选手（整个赛季中的最佳表现，而不是每轮的最佳表现）：

b
$ b

  SELECT playerID，matchID，playerForm，playerAge，MAX（matchRating）
 FROM PlayersMatch 
 INNER JOIN匹配ON PlayersMatch.matchID = Matches.matchID 
 WHERE Matches.matchSeason = 35 
 AND Matches.matchType ='L'
 AND PlayersMatch.position IS NOT NULL 
 GROUP BY PlayersMatch.playerID 
 ORDER BY MAX（matchRating）DESC，playerForm ASC 
  

我得到的问题是虽然我得到正确的playerID和玩家matchRating，我得到错误的matchID，形式，年龄和其他信息（即他们来自其他记录）。

我尝试将matchID添加到组中，并且当我得到正确的信息时，我有重复的记录，因为它产生了重复记录玩家（因为playerID和matchID组成了PlayersMatch中的PK）。

您对此的帮助非常感谢。

编辑：在进一步阅读之后，我必须错误地编写SQL，并且如果我只有playerID和max（matchRating），则group by只会返回正确的信息 - 至少是ANSI SQL正确的。

在这种情况下，如果我使用的是最大/组，则如何获得该性能的相应匹配详细信息？

$

  b $ b 
 编辑2：看起来我有一个正在运行的查询： SELECT * FROM PlayersMatch 
 INNER JOIN 
（SELECT playerID，MAX（matchRating）
 FROM PlayersMatch p2 
 JOIN匹配
 ON p2.matchID = Matches.matchID 
 WHERE matchSeason = 35 
 AND matchType ='L'
 AND p2.position IS NOT NULL 
 GROUP BY p2.playerID）AS p1 
 ON PlayersMatch.playerID = p1.playerID 
 AND PlayersMatch.matchRating = p1.matchRating 
 JOIN匹配m2 
 ON PlayersMatch.matchID = m2.matchID 
 WHERE m2.matchSeason = 35 
 AND m2.matchType = 'L'
 AND PlayersMatch.position不为空
 ORDER BY matchRating DESC 
  

现在唯一的问题是需要21秒才能运行。这个查询看起来是否正确？

解决方案

在PlayersMatch上添加第二个基于MatchID的索引仅用于初步资格加入Matches。在matchSeason上添加一个匹配表，然后输入。

在编辑和发布的数据样本中，我认为这样做可以解决第一个匹配问题，在同一个Rank下有多个实例。因此，再次，最内层的获得最佳MatchRating，因为您的MAX（）显然是在寻找最高评级。从那以后，它将立即重新加入到球员比赛中并获得具有相同评分的该人的FIRST比赛ID。最后，要关闭它，我们可以直接加入到名字信息的人员，并根据找到的第一个匹配ID匹配，因此不应该返回重复的内容......最终结果按照比赛排名进行排序。

  SELECT STRAIGHT_JOIN 
 Players.PlayerName，
 M2。*，
 PM.MatchRating， 
 PM.PlayerForm，
 PM.PlayerAge，
 PM.Position 
 FROM 
（选择PreMatch.PlayerID，
 PreMatch.MaxMatch，
 MIN（P3.MatchID）as FirstMatch 
 FROM 
（SELECT 
 p2.playerID，
 MAX（p2.matchRating）MaxMatch 
 FROM 
匹配
 JOIN PlayersMatch P2 
 ON Matches.MatchID = p2.matchID 
 AND P2.Position不为空
 WHERE 
匹配es.MatchSeason = 35 
 AND Matches.MatchType ='L'
 GROUP BY 
 p2.playerID）PreMatch 
 
 JOIN PlayersMatch P3 
 ON PreMatch。 PlayerID = P3.PlayerID 
 AND PreMatch.MaxMatch = P3.MatchRating 
 AND P3.Position不为空
 
 JOIN在P3.MatchID = M2上匹配M2 
。 MatchID 
 AND M2.MatchSeason = 35 
 AND M2.MatchType ='L'
 GROUP BY 
 PreMatch.PlayerID，
 PreMatch.MaxMatch 
）AS p1 
 
在P1.PlayerID = Players.PlayerID 
 
上加入玩家
在p1.FirstMatch = PM.MatchID 
上加入PlayersMatch PM 
 
 ORDER BY 
 p1.MaxMatch DESC 
  

I have a set of MySQL tables containing information from a football management game.

The tables are:

• Players - playerID (PK), playerName
• Matches - matchID (PK), matchSeason, matchRound, matchType
• PlayersMatch - playerID, matchID (comp PK), matchRating, playerForm, playerAge, position (can be null)

Data stored in these tables are related to the performance of a player. A Player plays in a match and has a rated performance (matchRating). There is a record in PlayersMatch for each match a player participates in, recording the players current form, match performance, their age at the time of the match (for historical purposes) and the position they played in.

Now, currently, I'm using the following query to list the top 10 players from the whole season (best performance in the season overall as opposed to best performance per round):

SELECT playerID, matchID, playerForm, playerAge, MAX(matchRating)
FROM PlayersMatch
INNER JOIN Matches ON PlayersMatch.matchID = Matches.matchID
WHERE Matches.matchSeason = 35
AND Matches.matchType = 'L'
AND PlayersMatch.position IS NOT NULL
GROUP BY PlayersMatch.playerID
ORDER BY MAX(matchRating) DESC, playerForm ASC

The problem I'm getting is that while I'm getting the right playerID and player matchRating, I'm getting the wrong matchID, form, age and other info (ie they're from other records).

I tried adding the matchID to the group by, and while I got the correct information, I had duplicates as it produced duplicate records for the players (because playerID and matchID make up the PK in PlayersMatch).

Your assistance in this is greatly appreciated.

Edit: After some further reading, I must be writing the SQL incorrectly and that a group by is only going to return the correct information if I just have playerID and max(matchRating) - at the very least to be ANSI SQL correct.

In that case, how do I get the corresponding match details for that performance if I'm using a max/group by?

Edit 2: Looks like I've got a working query:

SELECT * FROM PlayersMatch
INNER JOIN 
   (SELECT playerID, MAX(matchRating)
    FROM PlayersMatch p2
    JOIN Matches
      ON p2.matchID = Matches.matchID
    WHERE matchSeason = 35
    AND matchType = 'L'
    AND p2.position IS NOT NULL
    GROUP BY p2.playerID) AS p1
  ON PlayersMatch.playerID = p1.playerID
  AND PlayersMatch.matchRating = p1.matchRating
JOIN Matches m2
  ON PlayersMatch.matchID = m2.matchID
WHERE m2.matchSeason = 35
AND m2.matchType = 'L'
AND PlayersMatch.position IS NOT NULL
ORDER BY matchRating DESC

The only problem now is that it takes 21 seconds to run. Does this query look correct?

解决方案

Add a second index on PlayersMatch based on MatchID only for your preliminary qualification join to Matches. Add an index to your Matches table on matchSeason and type.

From your edit and posted data samples, I think this resolves to get the first "match" that qualifies the spanned multiple instances under a same "Rank". So, again, the inner-most gets the best MatchRating as your "MAX()" apparently is looking for the HIGHEST Rating. From that, it will immediately re-join to player matches and get the FIRST Match ID for that person with the same Rating. Finally, to close it out, we can directly join to the person for name info, and to the match based on the first match ID found, so no duplicates should be returned... That final result gets sorted per the match ranking..

SELECT STRAIGHT_JOIN
      Players.PlayerName,
      M2.*,
      PM.MatchRating,
      PM.PlayerForm,
      PM.PlayerAge,
      PM.Position
   FROM 
      ( select PreMatch.PlayerID,
               PreMatch.MaxMatch,
               MIN( P3.MatchID ) as FirstMatch
           FROM
               ( SELECT 
                    p2.playerID, 
                    MAX(p2.matchRating) MaxMatch
                 FROM 
                    Matches
                       JOIN PlayersMatch P2
                          ON Matches.MatchID = p2.matchID
                         AND P2.Position is not null
                 WHERE 
                        Matches.MatchSeason = 35
                    AND Matches.MatchType = 'L'
                 GROUP BY
                    p2.playerID ) PreMatch

               JOIN PlayersMatch P3
                   ON PreMatch.PlayerID = P3.PlayerID
                  AND PreMatch.MaxMatch = P3.MatchRating
                  AND P3.Position is not null

                  JOIN Matches M2
                     on P3.MatchID = M2.MatchID
                    AND M2.MatchSeason = 35
                    AND M2.MatchType = 'L' 
          GROUP BY
             PreMatch.PlayerID,
             PreMatch.MaxMatch
      ) AS p1

      JOIN Players
         on P1.PlayerID = Players.PlayerID

      JOIN PlayersMatch PM
          on p1.FirstMatch = PM.MatchID 

   ORDER BY 
      p1.MaxMatch DESC

这篇关于MySQL使用最大值选择细节的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！