如何从与比赛表相关的比赛表中得出比赛的获胜者？

Question

在存储锦标赛的数据库中，我有这样的关系结构来存储比赛及其比赛：

EER Diagram

当创建匹配时，与匹配中的游戏数量相对应地插入匹配行和多个游戏行。还会插入Match_left和match_right players行，然后将比赛中的两个players关联到match行。当玩家被记录为赢得了游戏时，与相应游戏相关的行被插入到game_winners表中。有没有一种方法可以编写一个查询来从获胜的游戏中推导出谁赢得了比赛？也许从逻辑上讲，这应该是一个视图，这样我就可以随时轻松地将匹配表行连接到它？

Here is some sample data

我预计弗雷德将作为match_ID 1的获胜者回归，因为他赢得了2/3的比赛。我希望match_ID 2的获胜者是空的，因为两个玩家都没有赢得必要的游戏数量才能获胜(2/3)。

Answer 1

让我们看看我是否正确理解了您的示例数据。

SELECT match_id, round_id, COUNT(*) AS game_count
  FROM games
  GROUP BY match_id, round_id

这将为您提供每场比赛中的游戏数量。这场比赛是一种最好的形式，所以你必须赢得超过一半的比赛数量才能赢得比赛。

SELECT match_id, round_id, player_id, COUNT(*) AS win_count
  FROM game_winners
  GROUP BY match_id, round_id

这将为您提供每个玩家在每场比赛中赢得的游戏数量。

SELECT match_id, round_id, player_id
  FROM game_winners
  GROUP BY match_id, round_id
  HAVING COUNT(*) * 2 > (
    SELECT COUNT(*)
      FROM games
      WHERE games.match_id = game_winners.match_id
      AND games.round_id = game_winners.round_id
  )

因此，一种选择是通过子查询添加一个检查，以确保玩家在比赛中赢得了超过一半的比赛。

SELECT games_won.match_id, games_won.round_id, games_won.player_id
  FROM (
    SELECT match_id, round_id, player_id, COUNT(*) AS win_count
      FROM game_winners
      GROUP BY match_id, round_id
    ) AS games_won
  INNER JOIN (
    SELECT match_id, round_id, COUNT(*) AS game_count
      FROM games
      GROUP BY match_id, round_id
    ) AS all_games
  ON games_won.match_id = game_count.match_id
  AND games_won.round_id = game_count.round_id
  WHERE games_won.win_count * 2 > all_games.game_count

或者，如果您愿意，也可以使用join来完成。

Answer 2

我在这里假设一场比赛的胜利者是赢得比赛最多的球员。

在一场比赛中对球员进行排名很简单。

SELECT player_id, COUNT(*) AS game_count
  FROM game_winners
  WHERE match_id = ?
  GROUP BY player_id
  ORDER BY COUNT(*) DESC

同时找到多场比赛的获胜者是一件很棘手的事情。

SELECT match_id, player_id, COUNT(*) AS game_count
  FROM game_winners
  GROUP BY match_id, player_id

让我们调用上面的player_wins

SELECT match_id, MAX(game_count) AS match_winner
  FROM player_wins
  GROUP BY match_id

这将为您提供每场比赛的获胜分数。

SELECT match_id, player_id
  FROM player_wins
  INNER JOIN (
    SEELCT match_id, MAX(game_count) AS match_winner
    FROM player_wins
    GROUP BY match_id
  ) AS winning_score
  ON player_wins.match_id = winning_score.match_id
  AND player_wins.game_count = winning_score.match_winner

这将为您提供每场比赛的获胜者。