LeetCode MySQL 1212. 查詢球隊積分

文章目錄

• • 1. 題目
  • 2. 解題

1. 題目

Table: Teams

+---------------+----------+
| Column Name   | Type     |
+---------------+----------+
| team_id       | int      |
| team_name     | varchar  |
+---------------+----------+
此表的主鍵是 team_id，表中的每一行都代表一支獨立足球隊。           

複制

Table: Matches

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| match_id      | int     |
| host_team     | int     |
| guest_team    | int     | 
| host_goals    | int     |
| guest_goals   | int     |
+---------------+---------+
此表的主鍵是 match_id，表中的每一行都代表一場已結束的比賽，
比賽的主客隊分别由它們自己的 id 表示，
他們的進球由 host_goals 和 guest_goals 分别表示。
 
積分規則如下：

赢一場得三分；
平一場得一分；
輸一場不得分。           

複制

寫出一條SQL語句以查詢每個隊的 team_id，team_name 和 num_points。

結果根據 num_points 降序排序，如果有兩隊積分相同，那麼這兩隊按 team_id 升序排序。

查詢結果格式如下：

Teams table:
+-----------+--------------+
| team_id   | team_name    |
+-----------+--------------+
| 10        | Leetcode FC  |
| 20        | NewYork FC   |
| 30        | Atlanta FC   |
| 40        | Chicago FC   |
| 50        | Toronto FC   |
+-----------+--------------+

Matches table:
+------------+--------------+---------------+-------------+--------------+
| match_id   | host_team    | guest_team    | host_goals  | guest_goals  |
+------------+--------------+---------------+-------------+--------------+
| 1          | 10           | 20            | 3           | 0            |
| 2          | 30           | 10            | 2           | 2            |
| 3          | 10           | 50            | 5           | 1            |
| 4          | 20           | 30            | 1           | 0            |
| 5          | 50           | 30            | 1           | 0            |
+------------+--------------+---------------+-------------+--------------+

Result table:
+------------+--------------+---------------+
| team_id    | team_name    | num_points    |
+------------+--------------+---------------+
| 10         | Leetcode FC  | 7             |
| 20         | NewYork FC   | 3             |
| 50         | Toronto FC   | 3             |
| 30         | Atlanta FC   | 1             |
| 40         | Chicago FC   | 0             |
+------------+--------------+---------------+           

複制

來源：力扣（LeetCode）

連結：https://leetcode-cn.com/problems/team-scores-in-football-tournament

著作權歸領扣網絡所有。商業轉載請聯系官方授權，非商業轉載請注明出處。

2. 解題

• 按照主隊，客隊分别計算完 合并，再group by，left join 隊名表

# Write your MySQL query statement below
select Teams.team_id, Teams.team_name, ifnull(Scores.score, 0) num_points
from Teams left join
(
    select team, sum(score) score
    from
    (
        (
            select host_team team, (case when host_goals > guest_goals then 3
                                    when host_goals = guest_goals then 1
                                    else 0 end) score
            from Matches
        )
        union all
        (
            select guest_team team, (case when host_goals > guest_goals then 0
                                    when host_goals = guest_goals then 1
                                    else 3 end) score
            from Matches
        )
    ) temp
    group by team
) Scores
on Teams.team_id = Scores.team
order by num_points desc, team_id           

複制

690 ms