文章目錄

• • 1. 題目
  • 2. 解題

1. 題目

學生表: Students

+---------------+---------+ | Column Name | Type | +---------------+---------+ | student_id | int | | student_name | varchar | +---------------+---------+

主鍵為 student_id（學生ID），該表內的每一行都記錄有學校一名學生的信息。

科目表: Subjects

+--------------+---------+ | Column Name | Type | +--------------+---------+ | subject_name | varchar | +--------------+---------+

主鍵為 subject_name（科目名稱），每一行記錄學校的一門科目名稱。

考試表: Examinations

+--------------+---------+ | Column Name | Type | +--------------+---------+ | student_id | int | | subject_name | varchar | +--------------+---------+

這張表壓根沒有主鍵，可能會有重復行。
學生表里的一個學生修讀科目表里的每一門科目，
而這張考試表的每一行記錄就表示學生表里的某個學生參加了一次科目表里某門科目的測試。

要求寫一段 SQL 語句，查詢出每個學生參加每一門科目測試的次數，結果按 student_id 和 subject_name 排序。

查詢結構格式如下所示：

Students table: +------------+--------------+ | student_id | student_name | +------------+--------------+ | 1 | Alice | | 2 | Bob | | 13 | John | | 6 | Alex | +------------+--------------+ Subjects table: +--------------+ | subject_name | +--------------+ | Math | | Physics | | Programming | +--------------+ Examinations table: +------------+--------------+ | student_id | subject_name | +------------+--------------+ | 1 | Math | | 1 | Physics | | 1 | Programming | | 2 | Programming | | 1 | Physics | | 1 | Math | | 13 | Math | | 13 | Programming | | 13 | Physics | | 2 | Math | | 1 | Math | +------------+--------------+ Result table: +------------+--------------+--------------+----------------+ | student_id | student_name | subject_name | attended_exams | +------------+--------------+--------------+----------------+ | 1 | Alice | Math | 3 | | 1 | Alice | Physics | 2 | | 1 | Alice | Programming | 1 | | 2 | Bob | Math | 1 | | 2 | Bob | Physics | 0 | | 2 | Bob | Programming | 1 | | 6 | Alex | Math | 0 | | 6 | Alex | Physics | 0 | | 6 | Alex | Programming | 0 | | 13 | John | Math | 1 | | 13 | John | Physics | 1 | | 13 | John | Programming | 1 | +------------+--------------+--------------+----------------+ 結果表需包含所有學生和所有科目（即便測試次數為0）： Alice 參加了 3 次數學測試, 2 次物理測試，以及 1 次編程測試； Bob 參加了 1 次數學測試, 1 次編程測試，沒有參加物理測試； Alex 啥測試都沒參加； John 參加了數學、物理、編程測試各 1 次。

來源：力扣（LeetCode）
鏈接：https://leetcode-cn.com/problems/students-and-examinations
著作權歸領扣網絡所有。商業轉載請聯系官方授權，非商業轉載請注明出處。

2. 解題

• 先進行內連接產生所有的組合

select * from Students st join Subjects sub{"headers": ["student_id", "student_name", "subject_name"], "values": [[1, "Alice", "Programming"], [1, "Alice", "Physics"], [1, "Alice", "Math"], [2, "Bob", "Programming"], [2, "Bob", "Physics"], [2, "Bob", "Math"], [13, "John", "Programming"], [13, "John", "Physics"], [13, "John", "Math"], [6, "Alex", "Programming"], [6, "Alex", "Physics"], [6, "Alex", "Math"]]}

• 再把考試表左連接于上表

{"headers": ["student_id", "student_name", "subject_name", "student_id", "subject_name"], "values": [[1, "Alice", "Programming", 1, "Programming"], [1, "Alice", "Physics", 1, "Physics"], [1, "Alice", "Physics", 1, "Physics"], [1, "Alice", "Math", 1, "Math"], [1, "Alice", "Math", 1, "Math"], [1, "Alice", "Math", 1, "Math"], [2, "Bob", "Programming", 2, "Programming"], [2, "Bob", "Physics", null, null], [2, "Bob", "Math", 2, "Math"], [13, "John", "Programming", 13, "Programming"], [13, "John", "Physics", 13, "Physics"], [13, "John", "Math", 13, "Math"], [6, "Alex", "Programming", null, null], [6, "Alex", "Physics", null, null], [6, "Alex", "Math", null, null]]}

• 再分組

# Write your MySQL query statement below select t.student_id, t.student_name, t.subject_name,sum(if(e.subject_name is null, 0, 1)) attended_exams from (select *from Students stjoin Subjects sub ) t left join Examinations e on t.student_id = e.student_id and t.subject_name = e.subject_name group by t.student_id, t.subject_name order by t.student_id, t.subject_name

720 ms

或者用 count(e.subject_name) attended_exams，null 項不會被計算，返回不為null

---

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

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

總結

以上是生活随笔為你收集整理的LeetCode MySQL 1280. 学生们参加各科测试的次数的全部內容，希望文章能夠幫你解決所遇到的問題。

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