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

Question

在存储锦标赛的数据库中，我使用以下关系结构来存储比赛及其比赛：

EER 图

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

这里是一些示例数据

我希望 Fred 作为 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

或者，如果您愿意，也可以使用联接来完成此操作。

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

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