Leetcode --- mysql 专题 --- 1841题：联赛信息统计

Leetcode — mysql 专题`

前言

     最近想把之前在leetcode练习的mysql专题梳理一遍，该系列主要是个人的学习总结，以方便之后个人时不时可以来翻阅复习。
     （若有侵权，请联系我，会立即删除文章）

一、问题描述

难度中等 1841. 联赛信息统计 - 力扣（LeetCode）

二、代码实现

1.代码思路

从结果出发，最终需要获取每个队伍参加的比赛场数、所得总分、在所有比赛获得总进球数、在所有比赛对手获得总进球数。
1.比赛场次：聚合后count()
2.所得总分：原来两个表都无分数相关数据可以直接使用，需要我们先根据条件判断每场比赛参与的两个队伍所得分数。
3.在所有比赛获得总进球数、在所有比赛对手获得总进球数：每支队伍在每场比赛是主场队伍或者是客场队伍，我们可以分为主场队伍和客场队伍分两次获取进球数再union

2.代码分步骤实现

（1）获得参与每场比赛的两个队伍的得分数

with m1 as
    (select m.*,
            (case
            when home_team_goals > away_team_goals then 3
            when home_team_goals = away_team_goals then 1
            else 0
            end ) as home_points,
            (case
            when home_team_goals < away_team_goals then 3
            when home_team_goals = away_team_goals then 1
            else 0
            end ) as away_points
    from Matches m)

（2）分为主场队伍和客场队伍分两次获取进球数再union

select m1.home_team_id tid,m1.home_team_goals  htg,m1.away_team_goals atg,m1.home_points p1
from m1
union all
select m1.away_team_id tid,m1.away_team_goals htg,m1.home_team_goals atg,m1.away_points p1
from m1 

（3）左连接上队伍名字，后group by 聚合，最后order by结果

3.完整代码

with m1 as
    (select m.*,
            (case
            when home_team_goals > away_team_goals then 3
            when home_team_goals = away_team_goals then 1
            else 0
            end ) as home_points,
            (case
            when home_team_goals < away_team_goals then 3
            when home_team_goals = away_team_goals then 1
            else 0
            end ) as away_points
    from Matches m)
select t.team_name,count(1) matches_played,sum(p1) points,sum(htg) goal_for,
        sum(atg) goal_against,sum(htg)-sum(atg)  goal_diff
from (
        select m1.home_team_id tid,m1.home_team_goals htg,m1.away_team_goals atg,m1.home_points p1
            from m1
        union all
        select m1.away_team_id tid,m1.away_team_goals htg,m1.home_team_goals atg,m1.away_points p1
            from m1 )m2
left join Teams t
on m2.tid = t.team_id
group by t.team_name
order by points DESC,goal_diff DESC,team_name

4.方法二（来自LeetCode 评论区）

关键在于：用OR进行JOIN

SELECT distinct team_name, 
    COUNT(*) AS matches_played,
    SUM(
        CASE 
        WHEN (team_id=home_team_id AND home_team_goals>away_team_goals) OR
             (team_id=away_team_id AND home_team_goals<away_team_goals) THEN 3
        WHEN (team_id=home_team_id AND home_team_goals<away_team_goals) OR 
             (team_id=away_team_id AND home_team_goals>away_team_goals) THEN 0 
        ELSE 1 END) AS points, 
    SUM(IF(team_id=home_team_id,home_team_goals,away_team_goals)) AS goal_for,
    SUM(IF(team_id=home_team_id,away_team_goals,home_team_goals)) AS goal_against,
    SUM(IF(team_id=home_team_id,home_team_goals,away_team_goals)) - SUM(IF(team_id=home_team_id,away_team_goals,home_team_goals)) AS goal_diff
FROM Teams JOIN Matches ON team_id=home_team_id OR team_id=away_team_id
GROUP BY team_id
ORDER BY points DESC, goal_diff DESC, team_name ASC

作者：yuiyue-2
链接：https://leetcode.cn/problems/league-statistics/solution/bu-yong-unionzhi-jie-dui-mei-ge-tong-ji-0okzt/
来源：力扣（LeetCode）
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

---

总结

    个人一开始没有理解作为主场队伍还是客场队伍的进球数和对手进球数的区别，故在第二步union中出错。
    本题用到了with...as..(  ) 和 case when...then.. end语句，是一道比较综合的题，关键要理清步骤。