php - mysql A队总进球数

Question

我正在尝试查找 A 队在过去 6 场比赛(总计)中的总进球数和失球数，无论 A 队是主队还是客队。例如:我们假设 $teamA = 'Arsenal' 并搜索最后 6 个结果，

给出以下结果:

Leicester City  1 - 1   Arsenal
Everton         2 - 2   Arsenal
Arsenal         2 - 1   Crystal Palace
Norwich City    0 - 2   Arsenal
Arsenal         1 - 0   West Bromwich Albion
Arsenal         3 - 0   Newcastle United

上面是获取最后 6 个结果的查询的输出，现在我需要获取 $teamA 和 $teamB 的进球总数

$query = mysql_query("SELECT * FROM football WHERE `HomeTeam` = '$teamA' OR AwayTeam = '$teamA' ");

while ($row = mysql_fetch_array($query)){

  if ($row['HomeTeam'] = $teamA){  
    $teamAgoals = $teamAgoals + $row['FTHG'];
  }


  if ($row2['AwayTeam'] = $teamA){  
    $teamAgoals = $teamAgoals + $row['FTAG'];
  }
  $tot++;

  if ($tot == 7):
    exit;
  endif;
}

我遇到的问题是查询中的 LIMIT 返回最后 6 个主场比赛或最后 6 个客场比赛。

Answer 1

您可以使用case表达式来确定球队是主队还是客队:

SELECT   SUM (CASE WHEN `HomeTeam` = '$teamA' THEN fthg ELSE ftag END)
FROM     football
WHERE    '$teamA' IN (`HomeTeam`, `AwayTeam`)
ORDER BY `MatchDate` DESC -- I'm guessing there's something like this
LIMIT    6