我最近开始练习SQL，之前使用MySQL只是为了管理网站数据库，但没有实际的SQL查询构造经验。以前有人问过这个练习的变体，但我想扩展以前解决方案的范围。这是一个足球（足球）得分练习，构建一个小联盟中不同游戏的汇总结果表。

TL;DR我设法得到了部分解决方案，但希望在最终表中再添加3列，并改进计算最终排名的方式。

我创建了一个包含4个团队的表格，并为每个团队分配了一个ID：

CREATE TABLE Teams AS
  SELECT 100 AS TEAM_ID, 'PUMP' AS TEAM UNION ALL
  SELECT 200, 'UP' UNION ALL
  SELECT 300, 'THE' UNION ALL
  SELECT 400, 'JAM';

其输出为：

SELECT * FROM Teams

还有一张table：
| 团队编号|团队|
| - ------|- ------|
| 一百|泵|
| 二百|向上|
| 三百|该|
| 四百|果酱|
然后，我创建了第二个表，按游戏显示比赛分数：

CREATE TABLE Matches AS
SELECT 1 AS MATCH_ID, 100 AS HomeTeam, 200 AS AwayTeam, 2 AS HomeScore, 0 AS AwayScore UNION ALL 
SELECT 2, 300, 400, 1, 1 UNION ALL
SELECT 3, 400, 100, 0, 1 UNION ALL
SELECT 4, 300, 200, 2, 3 UNION ALL
SELECT 5, 200, 400, 0, 0 UNION ALL
SELECT 6, 300, 100, 1, 0;

接收：
| 匹配ID|家庭团队|出发队|家庭评分|离开分数|
| - ------|- ------|- ------|- ------|- ------|
| 1个|一百|二百|第二章|无|
| 第二章|三百|四百|1个|1个|
| 三个|四百|一百|无|1个|
| 四个|三百|二百|第二章|三个|
| 五个|二百|四百|无|无|
| 六个|三百|一百|1个|无|
我能够创建一个总得分表，显示每个球队的总联赛积分，使用以下积分规则：

• 一队赢了得3分，
• 平局得1分，
• 队伍输了得0分

WITH cteHomePoints AS (SELECT HomeTeam AS Team,
                              CASE
                                WHEN HomeScore > AwayScore THEN 3
                                WHEN HomeScore = AwayScore THEN 1
                                ELSE 0
                              END AS Points
                         FROM Matches),
     cteAwayPoints AS (SELECT AwayTeam AS Team,
                               CASE
                                 WHEN AwayScore > HomeScore THEN 3
                                 WHEN AwayScore = HomeScore THEN 1
                                 ELSE 0
                               END AS Points
                          FROM Matches),
     cteAllPoints AS (SELECT Team, Points FROM cteHomePoints
                      UNION ALL
                      SELECT Team, Points FROM cteAwayPoints)
SELECT t.TEAM_ID, t.TEAM, COALESCE(SUM(ap.POINTS), 0) AS TOTAL_POINTS
  FROM Teams t
  LEFT OUTER JOIN cteAllPoints ap
    ON ap.TEAM = t.TEAM_ID
  GROUP BY  t.TEAM_ID, t.TEAM
  ORDER BY COALESCE(SUM(POINTS), 0) DESC, t.TEAM_ID

由此得出下表：
| 团队编号|团队|总计_点|
| - ------|- ------|- ------|
| 一百|泵|六个|
| 二百|向上|四个|
| 三百|该|四个|
| 四百|果酱|第二章|

但是，有两件事我想完成：

1.我想在最终表格中再添加3列，以显示每个团队：

• 目标得分
• 收到的目标
• 差异（评分和接收之间）

1.球队位置由分数决定，如果平局，位置由更好的“净胜球”决定，即得分减去收到的进球。这会影响最终排名（第二名和第三名将交换位置）。
我尝试了几种不同的方法，比如添加

cteHomeScore AS (SELECT HomeTeam AS Team,
                             END AS Scored
                         FROM Matches),
     cteAwayScore AS (SELECT AwayTeam AS Team,
                             END AS Received
                         FROM Matches),                               
     cteAllScored AS (SELECT Team, Scored FROM cteHomeScore
                      UNION ALL
                      SELECT Team, Received FROM cteAwayScore),

但我好像破解不了。
任何帮助都将不胜感激：）