添加一个窗口函数

太棒了！您现在已经得到一个结果集，能够检索比赛日期、主队、客队，以及双方的进球数。还剩下最后一个问题组件——曼联在每场比赛中"输得有多惨"？

为此，我们在主查询中添加一个窗口函数，按照 home_goal 与 away_goal 之差的"绝对值"对比赛进行排名。这样就可以直接比较分差，而无需考虑曼联是主场还是客场！

等式部分已经为您写好——您只需正确补全窗口函数即可！

本练习是课程的一部分

SQL 中的数据处理

查看课程

练习说明

设置 CTE，使主队与客队各自都包含名称、ID 和进球数。
在主查询中选择 date、home team name、away team name、home goals 和 away goals。
按比分差的大小对比赛进行排名，并按"降序"排序。

交互式实操练习

通过完成这段示例代码来试试这个练习。

-- Set up the home team CTE
___ ___ ___ (
  SELECT m.id, t.team_long_name,
	  CASE WHEN m.home_goal > m.away_goal THEN 'MU Win'
		   WHEN m.home_goal < m.away_goal THEN 'MU Loss' 
  		   ELSE 'Tie' END AS outcome
  FROM match AS m
  LEFT JOIN team AS t ON m.hometeam_id = t.team_api_id),
-- Set up the away team CTE
___ ___ (
  SELECT m.id, t.team_long_name,
	  CASE WHEN m.home_goal > m.away_goal THEN 'MU Loss'
		   WHEN m.home_goal < m.away_goal THEN 'MU Win' 
  		   ELSE 'Tie' END AS outcome
  FROM match AS m
  LEFT JOIN team AS t ON m.awayteam_id = t.team_api_id)
-- Select columns and and rank the matches by goal difference
SELECT DISTINCT
    ___,
    ___.___ AS home_team,
    ___.___ AS away_team,
    m.home_goal, m.away_goal,
    ___() ___(___ ___ ABS(home_goal - away_goal) ___) as match_rank
-- Join the CTEs onto the match table
FROM match AS m
___ JOIN ___ ON m.id = ___
___ JOIN ___ ON m.id = ___
WHERE m.season = '2014/2015'
      AND ((home.team_long_name = 'Manchester United' AND home.outcome = 'MU Loss')
      OR (away.team_long_name = 'Manchester United' AND away.outcome = 'MU Loss'));

编辑并运行代码