php - 每个团队每周仅获得 "football matchup"的一个结果

Question

我正在整理一个新项目，我需要做的事情之一是获取假足球(美国)比赛的结果。我在 php 和 mysql 中执行此操作，使用 CodeIgniter 作为我的框架。我的dbfiddle如下:

https://www.db-fiddle.com/f/5Q7QezddpNEGabaia2w5FQ/0

现在，如您所见，每个团队都有一个 home_team_id 和away_team_id 条目。我想做的是有一个查询，该查询只为每个球队获取一个结果(无论他们是主场还是客场)。如果不需要的话，我宁愿不以编程方式处理这些数据。

结果应该类似于以下内容:

SELECT week, home_team_id, away_team_id, 
    my_score, their_score, 
    a.team_name as home_team, b.team_name as away_team 
FROM nfl_user_matchups nm 
LEFT JOIN user_teams a ON nm.home_team_id = a.user_teams_id 
LEFT JOIN user_teams b ON nm.away_team_id = b.user_teams_id 
WHERE week = 1

预期的最终数据将是这样的(my_score 将是 home_team_id 得分，their_score 将是away_team_id 得分):

+------+--------------+--------------+----------+-------------+
| week | home_team_id | away_team_id | my_score | their_score |
+------+--------------+--------------+----------+-------------+
| 1    | 3            | 9            | 112      | 144         |
+------+--------------+--------------+----------+-------------+
| 1    | 7            | 2            | 85       | 96          |
+------+--------------+--------------+----------+-------------+
| 1    | 1            | 6            | 111      | 114         |
+------+--------------+--------------+----------+-------------+
| 1    | 4            | 5            | 99       | 125         |
+------+--------------+--------------+----------+-------------+
| 1    | 8            | 10           | 140      | 122         |
+------+--------------+--------------+----------+-------------+

Answer 1

您的问题陈述基本上意味着一对相互竞争的球队，即，(1,2)将被视为与(2,1 )。处理此类要求的一种方法是确保对于这两支球队之间的任何比赛，我们确保它们的顺序相同。所以，基本上我们得到 Least()两队中的team id值，始终放在第一个索引中；和 Greatest()值始终位于第二个(最后一个)索引。值得注意的是，Greatest() 与 Max() 不同。 Max() 函数计算列上的最大值；而Greatest()通常用于比较一行中的值。

现在，我们可以简单地对此对进行GROUP BY并根据需要计算聚合。在确定主/客场得分时，您需要使用 CASE .. WHEN 表达式来确定特定行是否具有最少的主场 id，或客场 id:

SELECT 
  week, 
  LEAST(home_team_id, away_team_id) AS home_id, 
  GREATEST(home_team_id, away_team_id) AS away_id, 
  MAX(CASE 
      WHEN LEAST(home_team_id, away_team_id) = home_team_id 
      THEN my_score 
      ELSE their_score
      END) AS home_score,
  MAX(CASE 
      WHEN GREATEST(home_team_id, away_team_id) = away_team_id 
      THEN their_score 
      ELSE my_score
      END) AS away_score 
FROM nfl_user_matchups 
WHERE week = 1
GROUP BY
  week, 
  home_id, 
  away_id;

View on DB Fiddle