테이블과 결과
Input:
Teams table:
+---------+-----------+
| team_id | team_name |
+---------+-----------+
| 1 | Ajax |
| 4 | Dortmund |
| 6 | Arsenal |
+---------+-----------+
Matches table:
+--------------+--------------+-----------------+-----------------+
| home_team_id | away_team_id | home_team_goals | away_team_goals |
+--------------+--------------+-----------------+-----------------+
| 1 | 4 | 0 | 1 |
| 1 | 6 | 3 | 3 |
| 4 | 1 | 5 | 2 |
| 6 | 1 | 0 | 0 |
+--------------+--------------+-----------------+-----------------+
Output:
+-----------+----------------+--------+----------+--------------+-----------+
| team_name | matches_played | points | goal_for | goal_against | goal_diff |
+-----------+----------------+--------+----------+--------------+-----------+
| Dortmund | 2 | 6 | 6 | 2 | 4 |
| Arsenal | 2 | 2 | 3 | 3 | 0 |
| Ajax | 4 | 2 | 5 | 9 | -4 |
+-----------+----------------+--------+----------+--------------+-----------+
Explanation:
Ajax (team_id=1) played 4 matches: 2 losses and 2 draws. Total points = 0 + 0 + 1 + 1 = 2.
Dortmund (team_id=4) played 2 matches: 2 wins. Total points = 3 + 3 = 6.
Arsenal (team_id=6) played 2 matches: 2 draws. Total points = 1 + 1 = 2.
Dortmund is the first team in the table. Ajax and Arsenal have the same points, but since Arsenal has a higher goal_diff than Ajax, Arsenal comes before Ajax in the table.
문제
Write an SQL query to report the statistics of the league. The statistics should be built using the played matches where the winning team gets three points and the losing team gets no points. If a match ends with a draw, both teams get one point.
Each row of the result table should contain:
- team_name - The name of the team in the Teams table.
- matches_played - The number of matches played as either a home or away team.
- points - The total points the team has so far.
- goal_for - The total number of goals scored by the team across all matches.
- goal_against - The total number of goals scored by opponent teams against this team across all matches.
- goal_diff - The result of goal_for - goal_against.
Return the result table ordered by points in descending order. If two or more teams have the same points, order them by goal_diff in descending order. If there is still a tie, order them by team_name in lexicographical order.
답
SELECT
t.team_name
,COUNT(1) AS 'matches_played'
,SUM(
CASE
WHEN m.team_score > m.opponent_score
THEN 3
WHEN m.team_score = m.opponent_score
THEN 1
ELSE 0
END
) AS 'points'
,SUM(m.team_score) AS 'goal_for'
,SUM(m.opponent_score) AS 'goal_against'
,SUM(m.team_score) - SUM(m.opponent_score) AS 'goal_diff'
FROM
(
SELECT
m1.home_team_id AS 'team_id'
,m1.home_team_goals AS 'team_score'
,m1.away_team_goals AS 'opponent_score'
FROM Matches m1
UNION ALL
SELECT
m2.away_team_id AS 'team_id'
,m2.away_team_goals AS 'team_score'
,m2.home_team_goals AS 'opponent_score'
FROM Matches m2
ORDER BY team_id
) AS m
JOIN Teams t ON m.team_id = t.team_id
GROUP BY t.team_id
ORDER BY points DESC, goal_diff DESC, team_name ASC
;