php - php/mysql-计数和分组

我有一个数据库，里面有玩过的游戏和他们的分数。现在我要运行一个查询，它计算每个球队的所有结果，每个球队的得分超过x（主客场加起来）。
数据库结构：

+----------+----------+---------------+---------------+
| HomeTeam | AwayTeam | HomeTeamGoals | AwayTeamGoals |
+----------+----------+---------------+---------------+
| team1    | team2    |             3 |             1 |
| team3    | team4    |             1 |             2 |
| team1    | team3    |             4 |             4 |
| team4    | team2    |             0 |             1 |
+----------+----------+---------------+---------------+

想要的结果：

+-------+----------+--------------------+
| team  | played   |  games > 3.5 goals |
+-------+----------+--------------------+
| team1 |        2 |                  2 |
| team2 |        2 |                  1 |
| team3 |        2 |                  1 |
| team4 |        2 |                  0 |
+-------+----------+--------------------+

我在看这样的东西：

SELECT *, COUNT(*) AS total FROM games WHERE homeTeamGoals + awayTeamGoals > 3.5 GROUP BY homeTeam

但这只适用于主场比赛，所以我需要一种方法来将客场比赛也加入其中。

最佳答案

分别得到HomeTeam和AwayTeam的结果。
使用UNION ALL合并结果
将结果用作Derived Table。
Group by在团队的派生表上，以获取结果。
尝试以下操作（SQL Fiddle DEMO）：

SELECT derived_t.team,
       SUM(derived_t.played) AS played,
       SUM(derived_t.games)  AS 'games > 3.5 goals'
FROM
(
  SELECT t1.HomeTeam as team,
         COUNT(*) AS played,
         SUM( IF(t1.HomeTeamGoals + t1.AwayTeamGoals > 3.5, 1, 0) ) AS games
  FROM your_table AS t1
  GROUP BY t1.HomeTeam

  UNION ALL

  SELECT t1.AwayTeam as team,
         COUNT(*) AS played,
         SUM( IF(t1.HomeTeamGoals + t1.AwayTeamGoals > 3.5, 1, 0) ) AS games
  FROM your_table AS t1
  GROUP BY t1.AwayTeam
) AS derived_t
GROUP BY derived_t.team

关于php - php/mysql-计数和分组，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/52567508/