1212. 查询球队积分（难度：中等）

题目描述

（通过次数8,714 | 提交次数17,042，通过率51.13%）

表: Teams
+---------------+----------+
| Column Name   | Type     |
+---------------+----------+
| team_id       | int      |
| team_name     | varchar  |
+---------------+----------+
此表的主键是 team_id。
表中的每一行都代表一支独立足球队。

表: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 分别表示。

您希望在所有比赛之后计算所有球队的比分。积分奖励方式如下:
如果球队赢了比赛(即比对手进更多的球)，就得 3 分。
如果双方打成平手(即，与对方得分相同)，则得 1 分。
如果球队输掉了比赛(例如，比对手少进球)，就 不得分 。
写出一条SQL语句以查询每个队的team_id，team_name 和 num_points。

返回的结果根据num_points 降序排序，如果有两队积分相同，那么这两队按team_id 升序排序。

查询结果格式如下。
示例 1:
输入：
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            |
+------------+--------------+---------------+-------------+--------------+
输出：
+------------+--------------+---------------+
| 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/problems/team-scores-in-football-tournament

//测试数据
Create table If Not Exists Teams (team_id int, team_name varchar(30));
Create table If Not Exists Matches (match_id int, host_team int, guest_team int, host_goals int, guest_goals int);

insert into Teams (team_id, team_name) values ('10', 'Leetcode FC');
insert into Teams (team_id, team_name) values ('20', 'NewYork FC');
insert into Teams (team_id, team_name) values ('30', 'Atlanta FC');
insert into Teams (team_id, team_name) values ('40', 'Chicago FC');
insert into Teams (team_id, team_name) values ('50', 'Toronto FC');

insert into Matches (match_id, host_team, guest_team, host_goals, guest_goals) values ('1', '10', '20', '3', '0');
insert into Matches (match_id, host_team, guest_team, host_goals, guest_goals) values ('2', '30', '10', '2', '2');
insert into Matches (match_id, host_team, guest_team, host_goals, guest_goals) values ('3', '10', '50', '5', '1');
insert into Matches (match_id, host_team, guest_team, host_goals, guest_goals) values ('4', '20', '30', '1', '0');
insert into Matches (match_id, host_team, guest_team, host_goals, guest_goals) values ('5', '50', '30', '1', '0');

解题思路

一场比赛有主客队双方参与，然后根据比赛进球数的对比，主队和客队分别获得不同的分数。所以，在统计球队的最终得分时，既要考虑球队作为主队的得分，也要考虑球队作为客队的得分。
那么，在计算最终得分时，可以先分别计算球队作为主客队的得分，然后再合并汇总。
最后，根据合并汇总后的得分排序即可。

参考SQL

未特别说明的情况下，参考SQL为基于MySQL8.0实现。

with
tmp1 as (
    select
        host_team team_id,
        case when host_goals > guest_goals then 3
             when host_goals = guest_goals then 1
             else 0
        end num_points
    from Matches
    union all
    select
        guest_team team_id,
        case when host_goals > guest_goals then 0
             when host_goals = guest_goals then 1
             else 3
        end num_points
    from Matches
),
tmp2 as (
    select
        team_id,
        sum(num_points) num_points
    from tmp1
    group by team_id
)
select
    a.team_id,
    a.team_name,
    coalesce(b.num_points,0) num_points
from Teams a
left join tmp2 b
on a.team_id = b.team_id
order by 3 desc,1;