Easy之1280.学生们参加各科测试的次数

秒速五厘米 2022-10-11 01:20 90阅读 0赞

### 学生表: Students ###

<table> 
 <thead> 
  <tr> 
   <th>Column Name</th> 
   <th>Type</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>student_id</td> 
   <td>int</td> 
  </tr> 
  <tr> 
   <td>student_name</td> 
   <td>varchar</td> 
  </tr> 
 </tbody> 
</table>

主键为 student\_id（学生ID），该表内的每一行都记录有学校一名学生的信息。

### 科目表: Subjects ###

<table> 
 <thead> 
  <tr> 
   <th>Column Name</th> 
   <th>Type</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>subject_name</td> 
   <td>varchar</td> 
  </tr> 
 </tbody> 
</table>

主键为 subject\_name（科目名称），每一行记录学校的一门科目名称。

### 考试表: Examinations ###

<table> 
 <thead> 
  <tr> 
   <th>Column Name</th> 
   <th>Type</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>student_id</td> 
   <td>int</td> 
  </tr> 
  <tr> 
   <td>subject_name</td> 
   <td>varchar</td> 
  </tr> 
 </tbody> 
</table>

这张表压根没有主键，可能会有重复行。  
学生表里的一个学生修读科目表里的每一门科目，而这张考试表的每一行记录就表示学生表里的某个学生参加了一次科目表里某门科目的测试。

### 问题 ###

要求写一段 SQL 语句，查询出每个学生参加每一门科目测试的次数，结果按 student\_id 和 subject\_name 排序。

### 示例 ###

查询结构格式如下所示：

##### Students table: #####

<table> 
 <thead> 
  <tr> 
   <th>student_id</th> 
   <th>student_name</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>Alice</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>Bob</td> 
  </tr> 
  <tr> 
   <td>13</td> 
   <td>John</td> 
  </tr> 
  <tr> 
   <td>6</td> 
   <td>Alex</td> 
  </tr> 
 </tbody> 
</table>

##### Subjects table: #####

<table> 
 <thead> 
  <tr> 
   <th>subject_name</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Math</td> 
  </tr> 
  <tr> 
   <td>Physics</td> 
  </tr> 
  <tr> 
   <td>Programming</td> 
  </tr> 
 </tbody> 
</table>

##### Examinations table: #####

<table> 
 <thead> 
  <tr> 
   <th>student_id</th> 
   <th>subject_name</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>Math</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>Physics</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>Programming</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>Programming</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>Physics</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>Math</td> 
  </tr> 
  <tr> 
   <td>13</td> 
   <td>Math</td> 
  </tr> 
  <tr> 
   <td>13</td> 
   <td>Programming</td> 
  </tr> 
  <tr> 
   <td>13</td> 
   <td>Physics</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>Math</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>Math</td> 
  </tr> 
 </tbody> 
</table>

##### Result table: #####

<table> 
 <thead> 
  <tr> 
   <th>student_id</th> 
   <th>student_name</th> 
   <th>subject_name</th> 
   <th>attended_exams</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>Alice</td> 
   <td>Math</td> 
   <td>3</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>Alice</td> 
   <td>Physics</td> 
   <td>2</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>Alice</td> 
   <td>Programming</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>Bob</td> 
   <td>Math</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>Bob</td> 
   <td>Physics</td> 
   <td>0</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>Bob</td> 
   <td>Programming</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>6</td> 
   <td>Alex</td> 
   <td>Math</td> 
   <td>0</td> 
  </tr> 
  <tr> 
   <td>6</td> 
   <td>Alex</td> 
   <td>Physics</td> 
   <td>0</td> 
  </tr> 
  <tr> 
   <td>6</td> 
   <td>Alex</td> 
   <td>Programming</td> 
   <td>0</td> 
  </tr> 
  <tr> 
   <td>13</td> 
   <td>John</td> 
   <td>Math</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>13</td> 
   <td>John</td> 
   <td>Physics</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>13</td> 
   <td>John</td> 
   <td>Programming</td> 
   <td>1</td> 
  </tr> 
 </tbody> 
</table>

##### 结果显示解释 #####

结果表需包含所有学生和所有科目（即便测试次数为0）：  
Alice 参加了 3 次数学测试, 2 次物理测试，以及 1 次编程测试；  
Bob 参加了 1 次数学测试, 1 次编程测试，没有参加物理测试；  
Alex 啥测试都没参加；  
John 参加了数学、物理、编程测试各 1 次。

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

### 解答 ###

select s.*, count(e.subject_name) as attended_exams
    FROM (select * from students s1 ,subjects s2) s
    left join examinations e 
    on s.student_id = e.student_id and s.subject_name = e.subject_name
    group by s.student_id,s.subject_name
    order by s.student_id,s.subject_name

##### 思路： #####

1.  先**交叉连结**两个表：对表Students和表Subjects求笛卡尔积，得到4\*3行，2+1列的表，即12行3列的新表s；
2.  再连结三个表：对刚刚得到的新表和表Examinations进行**左连结**（LEFT JOIN）;
3.  查询