56，SQL训练之，力扣，1212. 查询球队积分

最新推荐文章于 2024-08-08 18:03:40 发布

凡梦_leo

最新推荐文章于 2024-08-08 18:03:40 发布

阅读量692

点赞数 15

分类专栏： SQL实战训练文章标签： linux 服务器运维数据库 sql nosql leetcode

本文链接：https://blog.csdn.net/qq_55006020/article/details/140898476

版权

SQL实战训练专栏收录该内容

69 篇文章 0 订阅

订阅专栏

学习：知识的初次邂逅
复习：知识的温故知新
练习：知识的实践应用

一，原题力扣链接

. - 力扣（LeetCode）

二，题干

表: 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 分。
如果球队输掉了比赛(例如，比对手少进球)，就 不得分 。

编写解决方案，以找出每个队的 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             |
+------------+--------------+---------------+

三，建表语句

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)
Truncate table Teams
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')
Truncate table Matches
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')

四，分析

题解:

第一张表:球队表

字段:球队id,球队名称

第二张表:进球表

字段:场次id,主队,客队,主队进球,客队进球

要求:求出每个球队的场次分数,以场次分数排名,如果相同以球队id排名.

思路:

第一步,根据题目要求,

主队进球大于客队进球则3分,
主队进球小于客队进球则1分
相等则1分
反之一样

所以我们 case when语法可以新建2个列,拿到分数

新增加的2个列,一列是主队的得分,一列是客队的得分

第二步,根据上个表拆出来2个表; 然后分别对着2个表聚合,sum成绩

第三步,union all 竖着连接2个表

以第一列分组,sum聚合第二列得到

第四步,球队id 左连接上述表考虑到有的球队可能没有打比赛所以需要做连接

第五步,球队没有打比赛就是null 那么if以下给他一个0 最后按题目要求排序就好

五，SQL解答

with t1 as (
    select match_id, host_team, guest_team, host_goals, guest_goals,
       case when host_goals>guest_goals then 3
            when host_goals<guest_goals then 0
            when host_goals=guest_goals then 1  end as num1,
      case when guest_goals = host_goals then 1
           when guest_goals > host_goals then 3
           when guest_goals < host_goals then 0
          end as num2
       from matches
),t2 as (
    select host_team,sum(num1) sum_num1 from t1 group by host_team
    union all
    select guest_team,sum(num2) sum_num2 from t1 group by guest_team
),t3 as (
  select host_team,sum(sum_num1) as sum_num3 from t2 group by host_team
),t4 as (
  select team_id,team_name from teams
),t5 as (
    select team_id,team_name, sum_num3 from t4 left join t3 on t4.team_id=t3.host_team
),t6 as (
    select team_id,team_name,if(sum_num3 is null,0,sum_num3) as num_points  from t5 order by num_points desc ,team_id
)
select * from t6;