文章目錄

• • 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) scorefrom((select host_team team, (case when host_goals > guest_goals then 3when host_goals = guest_goals then 1else 0 end) scorefrom Matches)union all(select guest_team team, (case when host_goals > guest_goals then 0when host_goals = guest_goals then 1else 3 end) scorefrom Matches)) tempgroup by team ) Scores on Teams.team_id = Scores.team order by num_points desc, team_id

690 ms

---

我的CSDN博客地址 https://michael.blog.csdn.net/

長按或掃碼關注我的公眾號（Michael阿明），一起加油、一起學習進步！

總結

以上是生活随笔為你收集整理的LeetCode MySQL 1212. 查询球队积分的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。