Medium之1212.查询球队积分

曾经终败给现在 2022-08-30 01:51 94阅读 0赞

**Table: Teams**

<table> 
 <thead> 
  <tr> 
   <th>Column Name</th> 
   <th>Type</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>team_id</td> 
   <td>int</td> 
  </tr> 
  <tr> 
   <td>team_name</td> 
   <td>varchar</td> 
  </tr> 
 </tbody> 
</table>

此表的主键是 team\_id，表中的每一行都代表一支独立足球队。

**Table: Matches**

<table> 
 <thead> 
  <tr> 
   <th>Column Name</th> 
   <th>Type</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>match_id</td> 
   <td>int</td> 
  </tr> 
  <tr> 
   <td>host_team</td> 
   <td>int</td> 
  </tr> 
  <tr> 
   <td>guest_team</td> 
   <td>int</td> 
  </tr> 
  <tr> 
   <td>host_goals</td> 
   <td>int</td> 
  </tr> 
  <tr> 
   <td>guest_goals</td> 
   <td>int</td> 
  </tr> 
 </tbody> 
</table>

此表的主键是 match\_id，表中的每一行都代表一场已结束的比赛，比赛的主客队分别由它们自己的 id 表示，他们的进球由 host\_goals 和 guest\_goals 分别表示。

### 问题 ###

写出一条SQL语句以查询每个队的 team\_id，team\_name 和 num\_points。结果根据 num\_points 降序排序，如果有两队积分相同，那么这两队按 team\_id 升序排序。  
积分规则如下：

赢一场得三分；  
平一场得一分；  
输一场不得分。

### 示例 ###

**Teams table:**

<table> 
 <thead> 
  <tr> 
   <th>team_id</th> 
   <th>team_name</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>10</td> 
   <td>Leetcode FC</td> 
  </tr> 
  <tr> 
   <td>20</td> 
   <td>NewYork FC</td> 
  </tr> 
  <tr> 
   <td>30</td> 
   <td>Atlanta FC</td> 
  </tr> 
  <tr> 
   <td>40</td> 
   <td>Chicago FC</td> 
  </tr> 
  <tr> 
   <td>50</td> 
   <td>Toronto FC</td> 
  </tr> 
 </tbody> 
</table>

**Matches table:**

<table> 
 <thead> 
  <tr> 
   <th>match_id</th> 
   <th>host_team</th> 
   <th>guest_team</th> 
   <th>host_goals</th> 
   <th>guest_goals</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>10</td> 
   <td>20</td> 
   <td>3</td> 
   <td>0</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>30</td> 
   <td>10</td> 
   <td>2</td> 
   <td>2</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>10</td> 
   <td>50</td> 
   <td>5</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>20</td> 
   <td>30</td> 
   <td>1</td> 
   <td>0</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>50</td> 
   <td>30</td> 
   <td>1</td> 
   <td>0</td> 
  </tr> 
 </tbody> 
</table>

**Result table:**

<table> 
 <thead> 
  <tr> 
   <th>team_id</th> 
   <th>team_name</th> 
   <th>num_points</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>10</td> 
   <td>Leetcode FC</td> 
   <td>7</td> 
  </tr> 
  <tr> 
   <td>20</td> 
   <td>NewYork FC</td> 
   <td>3</td> 
  </tr> 
  <tr> 
   <td>50</td> 
   <td>Toronto FC</td> 
   <td>3</td> 
  </tr> 
  <tr> 
   <td>30</td> 
   <td>Atlanta FC</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>40</td> 
   <td>Chicago FC</td> 
   <td>0</td> 
  </tr> 
 </tbody> 
</table>

来源：力扣（LeetCode）  
链接：https://leetcode-cn.com/problems/team-scores-in-football-tournament  
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

### 解答 ###

##### 法一：UNION ALL #####

通过union all 把team\_id=guest\_team的分数拼接到team\_id=host\_team的后面构成新表，然后group by team\_id

select team_id,team_name,
            sum(case when host_goals>guest_goals then 3
                     when host_goals=guest_goals then 1
                     else 0
                end) num_points
    from teams t
        left join 
        (select host_team,guest_team,host_goals,guest_goals
         from matches
         union all
         select guest_team host_team,
                host_team guest_team,
                guest_goals host_goals,
                host_goals guest_goals
         from matches) tmp 
    on t.team_id = tmp.host_team
    group by team_id
    order by num_points desc,team_id

##### 法二：CASE WHEN罗列大法 #####

通过控制team\_id是等于host\_team还是guest\_team来罗列分成六种情况讨论，要注意，也要考虑team\_id不在（host\_team,guest\_team)中的情况，则要通过ifnull(,0)进行控制。

select team_id,team_name,
           ifnull(sum(
                      case when host_goals>guest_goals and team_id=host_team then 3
                           when host_goals=guest_goals and team_id=host_team then 1
                           when host_goals<guest_goals and team_id=host_team then 0
                           when host_goals<guest_goals and team_id=guest_team then 3
                           when host_goals=guest_goals and team_id=guest_team then 1
                           when host_goals>guest_goals and team_id=guest_team then 0
                      end),0) as num_points
    from teams,matches 
    group by team_id
    order by num_points desc,team_id

##### 法三：CASE WHEN中嵌套IF语句 #####

法三的基本思想和法二类似，但是巧妙的是，对于平局的情况，不需要区分team\_id=host\_team还是guest\_team，所以作为case when 的else情况，同时还要注意team\_id是否在（host\_team,guest\_team)中的情况

select team_id,team_name,
           sum(case when host_goals>guest_goals then if(team_id = host_team,3,0)
                    when host_goals<guest_goals then if(team_id = guest_team,3,0)
                    else if(team_id in (host_team,guest_team),1,0)
                end) as num_points
    from teams ,matches 
    group by team_id
    order by num_points desc,team_id