sql - SELECT 语句帮助

Question

首先，我知道这是一个非常模糊的标题——我对 SQL 的了解非常薄弱，所以我真的不知道我要问的细节，所以请多多包涵。

我有一个看起来像这样的表:

+--------+-----------+----------+--------+--------+
| gameid | team1     | team2    | score1 | score2 |
+--------+-----------+----------+--------+--------+
|      1 | New York  | Boston   |      1 |      2 |
|      2 | New York  | Boston   |      2 |      0 |
|      3 | Baltimore | New York |      3 |      1 |
|      4 | Boston    | New York |      3 |      0 |
+--------+-----------+----------+--------+--------+

其中gameid为唯一标识，score1对应team1的分数等

我需要的是一个查询，该查询将生成给定团队对所有对手的胜负总和。以纽约为例，查询将创建下表:

+-----------+------+--------+
| team      | wins | losses |
+-----------+------+--------+
| Baltimore |    0 |      1 |
| Boston    |    1 |      2 |
+-----------+------+--------+

Answer 1

我现在无法访问 SQL 框(所以这可能是语法弱的)而且我不是数据库结构的忠实粉丝，但这应该让你继续:

SELECT team, SUM(win) AS wins, SUM(loss) AS losses
FROM
 (SELECT team1 AS team, 
   CASE WHEN score1>score2 THEN 1 END AS win, 
   CASE WHEN score2>score1 THEN 1 END AS loss
  FROM YourTable
  UNION ALL
  SELECT team2 AS team, 
   CASE WHEN score2>score1 THEN 1 END AS win, 
   CASE WHEN score1>score2 THEN 1 END AS loss
  FROM YourTable)
GROUP BY team

另外，这忽略了关系。

注意:我制作了这个 CW -- 如果您可以改进它，请更新